SUMPRODUCT
Multiply two columns row by row, then add the answers.
Multiply two columns row by row and total the answers
One number: the sum of each row's product
Syntax
=SUMPRODUCT(column1, column2, [column2], ...)
| Argument | Required | What goes here |
|---|---|---|
column1 | Required | The first column to multiply, like C2:C7. |
column2 | Required | The column to multiply it against, the same height, like D2:D7. |
column2 | Optional | The column to multiply it against, the same height, like D2:D7. |
How to use SUMPRODUCT
SUMPRODUCT multiplies the columns together one row at a time and adds up the results. It does in one formula what a helper column of multiplications and a SUM underneath it would do in two steps.
The columns have to be the same length. That is the only rule and it is the one that gets broken.
In accounting its real job is weighting. An average of days overdue treats a two hundred pound invoice and a twenty thousand pound one as equally important, and weighting by amount says how old the money is rather than how old the invoices are.
That weighted figure, days sales outstanding, is the one that gets reported, and it is SUMPRODUCT divided by SUM.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=SUMPRODUCT(D2:D9,E2:E9)Every row's days multiplied by its amount, added up. Not a figure anyone reports on its own. | 375250 |
=ROUND(SUMPRODUCT(D2:D9,E2:E9)/SUM(E2:E9),1)Divided by the total, it becomes weighted average days overdue. This is the number that goes in the report. | 47.8 |
=ROUND(AVERAGE(D2:D9),1)The unweighted average of the same column, for comparison. The gap between the two is the whole argument for weighting. | 34.4 |
Notes
- The columns must be the same length or the formula refuses.
- SUMPRODUCT will also count rows meeting conditions, by multiplying tests together. COUNTIFS says the same thing far more legibly.
Related
A reference tells you what it does. A unit makes you use it.