Let try with simple sample:
.........A......B......C
1..............one....two
2.......a.......1......2
3.......b.......3......4
4.......a.......5......6
Request: Sum the data range B2:C4, with columnA="a", row1="two"
Aiming to sum the range
.........A......B......C
1..............one....two
2.......a..............2
3.......b...............
4.......a..............6
Do as following steps:
1/ to make vertical range with 1st condition: columnA="a" =>
A2:A4="a" looks like this {1;0;1} where TRUE(or FALSE) = 1(or 0)
2/ to make horizoned range with 2st condition: row1="two" =>
B1:C1="two" looks like this {0,1}
3/ Multiply the two condition to make conditional range:
(A2:A4="a")*(B1:C1="two") ={1;0;1}*{0,1}={0,1;0,0;0,1}
.........A......B......C
1...............0......1
2.......1.......0......1
3.......0.......0......0
4.......1.......0......1
4/ Multiply with data range:
(A2:A4="a")*(B1:C1="two")*B2:C4 = {0,1;0,0;0,1} * {1,2;3,4;5,6} = {0,2;0,0;0;6}
.........A......B......C
1..............one.....two
2.......a.......0......2
3.......b.......0......0
4.......a.......0......6
5/ Final SUM:
=SUMPRODUCT((A2:A4="a")*(B1:C1="two")*B2:C4)
=2+6=8
Note: "," in horizoned range, ";" in vertical range
Hope it is clear for you.
Bookmarks