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.

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.

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)) .

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.

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 number of episodes must be multiplied by all of the various cost drivers (i.e. the whole range D6:I9).
• the items such as Ward Days must be multiplied by their standard cost in order to convert everything into financial values.

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.

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).

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.

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.

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.

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.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.

file: xlsumproduct.htm

© meadinkent.co.uk 2017

Page Last updated Oct17

CMIDX S2 P7 Y