Let's see how the SUMPRODUCT formula works using this sample data:
...........A..........B..........C
1.......Fe.........Fi.........10
2.......Fo........Fum......22
3.......Foo......Bar.......17
4.......Foo......Gee......42
5.......Bar.......Fly.......19
You want to sum column C where column A = Foo and column B = Bar on the same
row.
=SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
Result = 17
Here's how it works...
SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So, we're
going to multiply some things and then get the SUM of that multiplication.
Each of these expressions will return an array of either TRUE (T) or FALSE
(F):
(A1:A5="Foo")
(B1:B5="Bar")
Fe = Foo = F
Fo = Foo = F
Foo = Foo = T
Foo = Foo = T
Bar = Foo = F
Fi = Bar = F
Fum = Bar = F
Bar = Bar = T
Gee = Bar = F
Fly = Bar = F
SUMPRODUCT works with numbers so we need to convert those logical values,
TRUE and FALSE, to numbers. One way to do that is to use the double unary
"--". It coerces TRUE to numeric 1 and FALSE to numeric 0:
--TRUE = 1
--FALSE = 0
--(A1:A5="Foo")
--(B1:B5="Bar")
--(Fe = Foo) = F = 0
--(Fo = Foo) = F = 0
--(Foo = Foo) = T = 1
--(Foo = Foo) = T = 1
--(Bar = Foo) = F = 0
--(Fi = Bar) = F = 0
--(Fum = Bar) = F = 0
--(Bar = Bar) = T = 1
--(Gee = Bar) = F = 0
--(Fly = Bar) = F = 0
Now, here's where the multiplication takes place.
We coerced the logical test arrays to numbers and the data in col C is
already numbers so now these 3 arrays are multiplied together:
0 * 0 * 10 = 0
0 * 0 * 22 = 0
1 * 1 * 17 = 17
1 * 0 * 42 = 0
0 * 0 * 19 = 0
We have the results of the multiplication (PRODUCTS) so we just add (SUM)
them up:
=SUMPRODUCT({0;0;17;0;0}) = 17
So:
=SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
Result = 17
