The basic function
Its primary purpose is to multiply corresponding components in the user defined arrays, and return the sum of those products. It takes the format =SUMPRODUCT((array1)*(array2)* ...). It can contain up to 30 different arrays.
Performing SUMs only where particular conditions are met
An Excel array function can be used to add up or count a selected range of values which meet a specified 'IF' criteria. It only allows a single condition to be specified. SumProduct can perform calculations using any number of different criteria (within the limit of 30 different arrays).
In the example above, cell E14 calculates the number of episodes meeting a condition that the Type of procedure (in cells B6:B9) must be an 'Emergency'. The formula is =SUMPRODUCT((C6:C9)*($B$6:$B$9="Emergency")). The value above it in cell E13 is similar to D13, only with an extra array condition added.
Instead of each array argument being a range of cells to be multiplied, one of the arrays has now become a condition (or criteria) specifying the items to be SUMmed.
You can use adapt this formula to include numerous different rules e.g. =SUMPRODUCT((C6:C9) * (F6:F9) * ($B$6:$B$9<>"Elective") * (F6:F9<5)). In this illustration episodes are multiplied by hours of physiotherapy only where the type is not elective and the number of hours of physiotherapy is less than 5.
Other forms and uses of the SumProduct function
All of the examples above contain the '*' operator to symbolise mulipication of each pair of values. The function can return other mathematical values by using /+ or - instead.
It can be adapted to perform a COUNT function by using a formula such as =SUMPRODUCT(($H$6:$H$9>450)*1). This counts the number of procedures where the Drugs cost per patient exceeds £450.
Calculating a weighted average
Excel does not contain a built in function to calculate a weighted average. It is however easy to do it using the SumProduct() function in a simple formula.
= SUMPRODUCT(B4:B6, C4:C6) / SUM(C4:C6)
Count Unique Items (in Pivot tables)
In a pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable.
For example, to count the unique occurences of a Customer/Item order, add a column to your database, with the heading 'CustItem'
In the first data row, enter a formula that refers to the customer and item columns. For example:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)
Copy the formula down to all rows in the database.
Then, add the field to the data area of the pivot table.
In this example, you can see that nine unique customers placed an order for binders, and there were 14 orders for binders.
Tags : Excel
