| Feedback | Conditional Sums | Arrays | Index| |

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 final section below illustrates how SUMPRODUCT() can easily replace many Array functions which some users are wary of using due to the peculiar key combinations.

It's primary purpose is to multiply corresponding components within some 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.

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 |
meadinkent.co.uk | |||||||

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

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.

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

Would you like to learn more?

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

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.

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.

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 2014 | Page Last updated Feb14 | CMIDX S2 P7 Y |