A to Z of Excel Functions: The PRODUCT Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the PRODUCT function.

The PRODUCT function

The PRODUCT function multiplies all the numbers given as arguments and returns the product.  For example, if cells A1 and A2 numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together.  You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2.

The PRODUCT function is useful when you need to multiply many cells together.  For example, the formula =PRODUCT(A1:A9, C1:C9) is equivalent to 

The PRODUCT function uses the following syntax to operate:

The PRODUCT function has the following arguments:

• number1: this is required and represents the first number or range you wish to multiply
• number2, …: this argument is optional and represents the additional number(s) or range(s) you want to multiply, up to a maximum of 255 arguments.

It should be noted that if an argument is an array or reference, only numbers in the array or reference are multiplied.  Empty cells, logical values, and text in the array or reference will be ignored.

PRODUCT is often used in financial modelling.  Consider the following example:

In this illustration, you might not understand what the MOD function does, but hopefully, you can follow each of the flags in rows 4 to 7 without being an Excel guru.  Row 9, the product, simply multiplies all of the flags together (using the PRODUCT function allows you to add additional conditions / rows easily).  This effectively produces a sophisticated AND flag, where all of the formulae are mercifully short. 

Some might wonder why I use PRODUCT rather than MIN here.  I confess it is partly a preference and partly the fact that if you are modelling optimisation problems, MIN can give rise to non-smooth outputs (not a good thing) whereas PRODUCT does not. 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.