SUMPRODUCT() in Excel
By far, the most flexible and useful function for Excel formulas that I have found for my purposes is SUMPRODUCT. It is extremely useful, but not particularly obvious. Here I attempt to explain how it works, how it can be tricked into doing cool things with data, and how I use it in two spreadsheets I have used on a semi-daily basis for about ten years.
An Explanation of SUMPRODUCT
Sumproduct multiplies each element of its parameters together and then adds the results.
Example
Given the following table:
| A | B | C | |
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 |
And a formula that looked like this:
SUMPRODUCT(A:A, B:B, C:C)
You would get back a value of 36. This is derived like so:
First the values in columns A, B, and C are multiplied together: 1*1*1=1 2*2*2=8 3*3*3=27 Then they are added together: 1+8+27=36
I'm sure there are times when you would want to use the function for exactly this purpose, but I've never encountered one. Instead, I like to trick it into doing cool tests.
Tricking SUMPRODUCT
SUMIF is great when you want to add a bunch of cells together based on the value of that or another cell. But what sucks about it is that it can only test one condition (cell value) at a time. I tried many different ways of forcing multiple conditions on SUMIF, but all were either incredibly awkward or simply didn't work.
By replacing normal column values with conditions, we can get SUMPRODUCT to work like SUMIF without the single condition restriction. The trick is really quite simple and looks like this:
SUMPRODUCT((A:A=B:B)*1, C:C)
The first parameter is (A:A=B:B)*1. It is a condition using the equality operator (=). You can read (A:A=B:B) as "items in column A equal items in column B." As SUMPRODUCT cycles through each row of data, this test will return a TRUE or FALSE value for each test. The *1 portion multiplies the conditional result by 1, which is a short and easy way to turn TRUE and FALSE into 1 and 0.
The second parameter is simply a range in the C column.
All together, the statement can be read as "sum the values in column C where the values in columns A and B are equal." Of course, you could even switch the order of the statements around to read that way, like SUMPRODUCT(C:C, (A:A=B:B)*1). My personal preference is to have the condition as in my example, but it's up to you.
Example
Let's see how our conditional trick statement works using another example table:
| A | B | C | |
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 3 |
| 3 | 3 | 6 | 5 |
And here is the formula again:
SUMPRODUCT((A:A=B:B)*1, C:C)
Let's see how SUMPRODUCT sees our data.
SUMPRODUCT((1=1)*1, 1) which is (TRUE*1, 1) which is (1, 1) which is 1*1=1 SUMPRODUCT((2=2)*1, 3) which is (TRUE*1, 3) which is (1, 3) which is 1*3=3 SUMPRODUCT((3=6)*1, 5) which is (FALSE*1, 5) which is (0, 5) which is 0*5=0 When added together, the results of the three rows are: 1+3+0 = 4
Multiple Tests with SUMPRODUCT
Once we know how to trick SUMPRODUCT into summing columns of numbers based on one condition, adding more conditions is very simple. We simply add the conditions as additional SUMPRODUCT parameters.
Go from this... SUMPRODUCT((A:A=B:B)*1, C:C) ...to this... SUMPRODUCT((A:A=B:B)*1,(A:A=C:C)*1,(A:A=D:D)*1, E:E) ...and beyond.
Real-World Example One: A Budget and Ledger Sheet
Off and on for the last ten years, I have experimented with various forms of personal financial ledger keeping and budgeting. My Excel sheet has evolved over the years to something that is simple but quite powerful. At the core of the sheet is a formula that makes use of conditionals with SUMPRODUCT.
My Budget/Ledger has the following structure:
| A | B | C | D | |
| ... | ||||
| 3 | Category | Jan | Feb | Mar |
| 4 | Food | 123 | 142 | 103 |
| 5 | Water | 15 | 7 | 11 |
| 6 | Books | 321 | 233 | 156 |
| ... | ||||
| 42 | Date | Amount | Category | Description |
| 43 | 12-Jan-2008 | 13 | Books | cookbook |
| 44 | 25-Jan-2008 | 2 | Water | 8 gallons |
| 45 | 3-Feb-2008 | 67 | Food | weekly food |
| ... | ||||
Everything is in a single sheet. The top section has two important features. Column A holds the ledger item category names. Row 1 holds the abbreviated month names.
The bottom section starting on Row 42 contains the ledger entries for each expenditure. For each entry, Column A holds the date, Column B holds the dollar amount, and Column C holds the category name.
To get the table of monthly values for each category in the top section, I use the following formula:
=SUMPRODUCT( ($C$42:$C$2064=A4)*1, (MONTH($A$42:$A$2064) = MONTH(C$3&"-2008"))*1, $B$42:$B$2064 )
Of course, in Excel I have to have all of that on one line (sigh). This looks like a lot, but it's not too bad when broken down into sections.
The first test condition is $C$42:$C$2000=A4)*1 which can be read as "if the category at entry C42 to C2000 equals the one at A4..." The $ signs make the formula use absolute references so that when I duplicate the formula, it won't shift the references. 42 refers to the first row where ledger entries begin. 2000 is an arbitrary large number I am not likely to reach. You can think of $C$42:$C$2000 as being equivalent to something like "C:C" from the first examples. A4 is a reference to a specific category name from the top section.
The second test condition is (MONTH($A$42:$A$2000)=MONTH(C$3&"-2008"))*1 which can be read as "if the month portion of the date at entry A42 to A2000 equals the month at C3 in the year 2008..." This is very similar to the first condition, but instead of checking the category name, we are checking the entries' dates against a month value created from an abbreviated month name in Row 3.
Lastly, we sum the dollar amounts with the third parameter, $B$42:$B$2000.
All together in plain English, the formula can be read as, "add the dollar amounts in ledger column B where ledger column A matches the month in row 3 and ledger column C matches the category name in top section column A."
Real World Example Two: My Reading Journal
In 1993 I was given a school assignment to keep a journal of every book I read. I am still keeping that journal today. The journal spreadsheet contains a number of interesting formulas for gathering averages, page counts, etc. SUMPRODUCT came in quite handy when I decided to try creating a chart where each bar represented three months of reading (page count) over the journal's lifetime. It looks something like this:
| A | B | C | D | |
| 1 | Date | Title | Author | Pages |
| 2 | 12-Feb-2001 | How to Explode | Fink, Rat | 432 |
| ... | ||||
The first thing I did was start a new sheet and populate it with a column of dates where each date was a leap of three months.
| A | B | |
| 1 | Sep-93 | |
| 2 | Dec-93 | |
| 3 | Mar-94 | |
| To the present... | ||
To get a page count for the books read in each three month period, I needed two tests:
- The date from the journal is greater than this date
- The date from the journal is less than the next date
Given that cells from the Journal sheet can be referenced using the "!" operator, the two test conditions look like this:
(Journal!A:A > A1) And (Journal!A:A < A2)
Put into a formula, it takes shape as:
=SUMPRODUCT((Journal!A:A>A1)*1, (Journal!A:A<A2)*1, Journal!D:D)
In reality, Row 1 of the Journal sheet confused SUMPRODUCT if it was included, so absolute references had to be used and the formula became:
=SUMPRODUCT((Journal!A$2:A$1000>A1)*1, (Journal!A$2:A$1000<A2)*1, Journal!D$2:D$1000 )
Then it was simply a matter of duplicating the formula down the B column alongside the dates I had created...
| A | B | |
| 1 | Sep-93 | 1850 |
| 2 | Dec-93 | 1243 |
| 3 | Mar-94 | 1627 |
| ... | ||
In the end, I had a nice pair of columns from which to make my bar chart. I was quite pleased.
