| 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. Cell values can be edited

An Excel web app - Microsoft appear to have disabled the download button in the web app

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.

Since Excel 2007 it has been possible to use entire columns within SUMPRODUCT formulas, rather than only ranges of cells. This may simplify some calculations but it may reveal a problem. Many tables of data include column headings containing a description. If you attempt to multiply a range of values containing a text value, it will cause a #VALUE error.

Sumproduct applied to whole columns

The formula in H4 uses multiply operators, but if these are replaced with commas the error disappears. The formula =SUMPRODUCT((C:C=G4)*1,(D:D),(E:E)) uses commas instead of multiply and avoids the problems caused by the 'Sales' and 'Price' headings. This can also help if a range of cells can possibly include some text among the values.

Sumproduct containing a text value

For some reason the above set of data includes a text value in cell M5. This causes an error if the multiply operator is used and can be avoided using commas as an alternative. Having text within a range of values may be unexpectedly corrupting your results and the absence of a #VALUE error hides this problem. It may be worth using a formula to count text items in the range and add a warning message.

The above example also demonstrates (in cell P6) the possibility of replacing the '*1' with double unary operators i.e. two minus signs. Some authors prefer these, but I am not aware that their use affects performance. It is a matter of choice.

The purpose of the double unary (--) is to convert a Boolean TRUE or FALSE into a value of 1 or 0. The formula =(5>3) would return TRUE (because 5 is greater than 3). If it was modified to =--(5>3) it would return '1' which might be more useful.

The SUMPRODUCT 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 2017 Page Last updated Oct17 CMIDX S2 P7 Y