| Feedback | Conditional Sums | Arrays | Index|

SumProduct function: an alternative to using arrays for conditional calculations

SUMPRODUCT() is an extremely versatile function which can be used in many different ways. Excel Help gives only a limited idea as to the many uses of this tool. The second section below illustrates how SUMPRODUCT() can easily replace many Array functions which some users are wary of using due to the peculiar key combinations. The page also contains an Excel web app which you can edit or download.

open and save this free reference page with examples of 8 Excel functions

Click to download this document

1. The basic function

It's primary purpose is to multiply corresponding components within some user defined arrays (ranges), and return the sum of those products. It takes the format =SUMPRODUCT((array1)*(array2)* ...). It can contain up to 30 different arrays.

1 Hours Rate
2 8 10
3 4 25
4 Cost 180
(8 x 10) + (4 x 25) = 180

The following example shows a simple model to calculate the cost of four different clinical procedures. Six main elements (or cost drivers) have been identified: days spent on a ward; the time spent in operating theatre; the hours of physiotherapy; the number of outpatient attendances required; the cost of drugs and the cost of dressings. If the number of patient episodes (in column C) are multiplied by the quantity of a cost driver (e.g. hours of physiotherapy) you can calculate the total quantity of physiotherapy resources that will be required i.e. (80x6) + (45x1) + (150x2). This lengthy formula (in cell F10) can be replaced by =SUMPRODUCT(($C$6:$C$9)*(F6:F9)) .

- A B C D E F G H I
4 Procedure Costing model
5 Proc Type Episodes Ward Days Theatre Hrs Physio OutPat Drugs Dressings
6 A Emergency 80 4.00 1.50 6.00 3.00 480 250
7 B Elective 45 3.00 0.50 1.00 3.00 280 200
8 C Elective 50 3.50 0.50 0.00 1.00 1350 100
9 D Emergency 150 5.00 0.75 2.00 3.00 400 350
10   Total Resources 325 1380 280 825 875 178500 86500

[a] A table of activity data for a costing model

2. 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 illustration [c] below, 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.

A study by accountants Coopers and Lybrand has shown that 90% of spreadsheets with more than 150 rows contain errors. Check out my information on creating well designed spreadsheets.

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 modify 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. The answer is 300.

3. Using more complex arrays

For the data in the table above to become a useful costing model it needs two further developments:

The following table contains the costs of each element (e.g. each day a patient occupies a bed on a ward costs 87). Note that drugs and dressings are already specific cost values and therefore have no common costs.

- D E F G H I
1 Cost / Day Cost / Hour Cost / Hour Cost / Attend Value Value
2 87.00 685.00 19.00 23.00 1.00 1.00

[b] Standard costs for each cost driver

SUMPRODUCT() will accept the whole range of cost drivers as a single array (D6:I9) and will also allow you to enter another array of costs (D2:I2) which is the same width but of a different depth (only 1 row).

- C D E F
12 Report Total Emergency Elective
13 Total cost 612660 453942 158717
14 Episodes 325 230 95
15 Avg cost 1885 1974 1671

[c] Outputs of the costing model using the SumProduct function

The formula in D13 multiplies the numbers of episodes by the quantity of resources and by the cost of each element. =SUMPRODUCT((C6:C9)*(D6:I9)*(D2:I2)). The final row (15) shows the average cost of each type of procedure and is simply calculated by dividing the total cost by the number of episodes.

<< An interactive Excel Web App

An Excel web app - click on button at bottom of app to download into Excel

4. Other forms and uses of the SumProduct function

All of the examples above contain the '*' operator to symbolise multiplication 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.

The function is ideally suited to calculating weighted averages - something which Excel does not provide a specific function for.

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xlsumproduct.htm Open MeadInKent Facebook page 2016 Page Last updated Feb14 CMIDX S2 P7 Y