In recent blog posts, we’ve looked at the pivot table Count function, the Average function, and the Min and Max functions. In this article we’ll look at the Product function.

Product Summary Function

The Product summary function shows the result of multiplying all the underlying values in the Values area. The result is the same as using the PRODUCT function on the worksheet to calculate the multiplied values.

In all the years that I've worked with pivot tables, I've never had to use the Product summary function, and can't imagine a situation where it would be useful. However, you can see how it works, and decide if it would be useful in any of your applications.

PRODUCT Worksheet Function

The PRODUCT worksheet function can be useful as a quick way to multiply several values in a worksheet row. For example, in this list of orders, the PRODUCT function multiplies the item quantity, times the cost, times the markup rate, to get the total price for each order.

In column G, you can see the total calculated by using the multiplier operator instead. The only case in which the results are different is where there is a blank cell, such as in row 4. The PRODUCT function treats the blank as a 1, and the multiplier operator treats the blank as a zero.

In this scenario, the PRODUCT worksheet function shows a meaningful result.

PRODUCT Worksheet Function for Columns

However, when you're using data  as the source for a pivot table, you aren't multiplying rows, as you would with the PRODUCT worksheet example above. The pivot table will summarize the data from several, or even thousands of rows, so the PRODUCT function isn't useful, in my experience.

In the screen shot below, you can see the pivot table source data, with the product calculated for each type of item that's sold, using the PRODUCT worksheet function. Instead of multiplying the values in rows, values in the same column (Qty) are being multiplied.

At the bottom of the source data is the overall PRODUCT calculation.

Unlike the previous example, these products are meaningless.

Pivot Table Product Summary Function

When you use the Product summary function in a pivot table that's based on the data source shown above, the results are the same meaningless numbers.

The results of the Product function may be very large numbers and default to a Scientific number format. You can format the numbers as Number format, instead of Scientific format.

Note: Excel only stores and calculates with 15 significant digits of precision, so after the 15th character you'll only see zeros.

_________