SUMPRODUCT( SUMIF() ) - How does this work?

Question

Part 1:

I was able to construct a formula that does exactly what I want (from some examples), but yet, I'm unable to figure out how exactly it works. I have, starting with cell A1:

Price   $
table   20
chair   10

Invoice Quantity
table   17
chair   1
chair   2
table   3

What I want is the final total (430) for the invoice which is computed as Quantity*Price for each item (17*20 + 1*10 + 2*10 + 3*20). the following formula correctly does this:

=SUMPRODUCT(B6:B9,SUMIF(A2:A3,A6:A9,B2:B3))

I understand the basics of SUMPRODUCT and SUMIF. But here, my argument for SUMIF's range is A2:A3, which makes me think the SUMIF would iterate through A2 and A3, and not through A8:A11 (which is the criteria). What gives?

Edit: the unclear part is, what exactly does SUMIF do (what is its iteration pattern) when the first two arguments are of different dimensions (here, the range is 2 cells while the criteria is 4 cells). Also, what is the "output" of SUMIF? An array? Of what dimensions?

Part 2:

In addition, if I ignored the quantity and simply wanted to add 20 whenever I saw a table and 10 whenever I saw a chair, I figured I would do:

=SUMIF(A2:A3,A6:A9,B2:B3)

But that doesn't work, and I have to enclose it with a SUMPRODUCT() for it to work and correctly evaluate to 60. Enclosing it within a SUM doesn't work either (probably because the SUMIF doesn't return an array?) Why?

I've read a bunch of tutorials and still can't understand this, and would be most grateful for a clear, intuitive explanation for both these cases. Thank you.

Perhaps a step-by-step explanation (in algorithm style) of what Excel does with my formulas above (or how it iterates through them) would be one way to show how this all works. — PonyEars

brettdj brettdj · Accepted Answer · 2012-07-14T05:25:54

SUMIF can produce an array of results. If you take my formula
=SUMIF(A6:A9,A2:A3,B6:B9)
it says

For the criteria in A2 (ie table) - look at A6:A9
- where table is matched, sum the corresponding value in B6:B9
- returns 20 (ie 17 +0 +0 +3)
- this is stored in the first position of the array

Then for the criteria in A3 (ie chair)
- look at A6:A9
- where table is matched, sum the corresponding value in B6:B9
- returns 3 (ie 0 +1 +2 +0)
- this is stored in the second position of the array

So the end array from the SUMIF is {20:3}

You can see the array result by highlighting the SUMIF formula in Excel's formula bar and then pressing F9

Then use SUMPRODUCT to multiple the count in the SUMIF by the $ values in B2:B3 to get total dollars

={20;3}*{20:10}
=20*20 + 3*10
= 430

Part 1
Rather than
SUMIF(A2:A3,A6:A9,B2:B3)
which produces a four element array of
={20;10;10;20}
(corresponding to table;chair;chair;table)

You should use
SUMIF(A6:A9,A2:A3,B6:B9)
which sums the values in B6:B9 against your two criteria in A2:A3 giving the desired result
={20;3}
(corresponding to table;chair)

and then use SUMPRODUCT to weight your array, ie =SUMPRODUCT(SUMIF(A6:A9,A2:A3,B6:B9),B2:B3) ={20;3}*{20:10}
=430

Part 2
Use COUNTIF to return an array of the number of chairs and tables and then multiply by the vales using SUMPRODUCT
=SUMPRODUCT(B2:B3,COUNTIF(A6:A9,A2:A3))
={20;10} * {2;2}
=60

SUMPRODUCT( SUMIF() ) - How does this work?

Part 1:

Part 2:

2 Answers