|
|
|||
SUMPRODUCT() is an extremely versatile function which can be used in 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.
Click to download this document
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.
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 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 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 2013 | Page Last updated Dec11 | CMIDX S2 P7 Y |
