Charlie, the following may help you to understand SUMPRODUCT, and there is a
load of example syntx listed at the end.
The formula exploits the fact that Excel interprets TRUE as 1 and FALSE
as 0. Take the formula below:-
=SUMPRODUCT((A9:A20=A1)*(B9:B20="A")*(C9:C20))
This sets up an array that gives you something that looks like this
(depending on the variables of course):-
A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6
Which because TRUE=1 and FALSE=0, is interpreted as:-
A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35
and the SUM bit just adds up all the end values of the products
If you exclude the last part of the formula, so that it becomes:-
=SUMPRODUCT((A9:A20=A1)*(B9:B20="A"))
then what you end up with is a sum of a bunch of TRUE/FALSE values depending on
whether or not the criteria has been met on that row, and this is the same as
counting the number of records that meet your criteria. Imagine the above
tables without Column C, and the last one would look like the following:-
A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
-------------
8
SumProduct - Examples of Syntax
=SUMPRODUCT(($A$1:$A$100="L")*(CODE($B$1:$B$100)=66))
=SUMPRODUCT(($A$1:$A$100="L")*(CODE(UPPER($B$1:$B$100))=66))
=SUMPRODUCT(($A$1:$A$100="L")*(LEFT($B$1:$B$100,1)="b"))
=SUMPRODUCT(($B$6:$B$14=E6)*($C$6:$C$14=F6)*($D$6:$D$14))
=SUMPRODUCT((A1:A100="apples")*(C1:C100="Spain")*(D1
100))
=SUMPRODUCT((A1:A100>=$E$1)*(A1:A100<=$E$2)*(B1:B100=$E$3)*(C1:C100=$E$4)*D1
10
0)
=SUMPRODUCT((A1:A100>=1)*(A1:A100<=100)*A1:A100)/SUMPRODUCT((A1:A100>=1)*(A1:A10
0<=100))
=SUMPRODUCT((A1:A20<0)*(B1:B20))/SUMPRODUCT((A1:A20<0)*1)
=SUMPRODUCT((A1:A20=13)*(B1:B20>6)*C1:C20)
=SUMPRODUCT((A1:A20=2003)*(B1:B20=1)*C1:C20)
=SUMPRODUCT((A1:A365>=DATEVALUE("10/01/2002")) *
(A1:A365<DATEVALUE("11/01/2002")) * (C1:C365))
=SUMPRODUCT((A1:A4="X")*(B1:B4="N")*C1:C4)
=SUMPRODUCT((A2:A100="red")+(A2:A100="blue")+(A2:A100="yellow"),B2:B100)
=SUMPRODUCT((A2:A100={"red","blue","yellow"})*B2:B100)
=SUMPRODUCT((A2:A105=D1)+(A2:A105=E1)+(A2:A105=F1),B2:B105)
=SUMPRODUCT((A2:A120={"red","blue","yellow"})*(B2:B120))
=SUMPRODUCT((A2:A120=D1:F1)*(B2:B120))
=SUMPRODUCT((A2:A120=D1:F1)*(B2:B150))
=SUMPRODUCT((A2:B100="YourClass")*(B2:B100="YourSize")*C2:C100)
=SUMPRODUCT((A8:A200="Jan")*(B2:B10="Week 1")*H8:H200)
=SUMPRODUCT((A9:A25="blue")*ABS(C9:C25))
=SUMPRODUCT((B40:AE40=TRUE)*(B41:AE41="V"))
=SUMPRODUCT((dts>st)*(dts<=nd)*(B4:M4))
=SUMPRODUCT((EXACT(LEFT(data,1),"s")+0))
=SUMPRODUCT((LEFT(INDIRECT($B23&"!H2:H4500"),4)=C$3)*(INDIRECT($B23&"!F2:F4500")
=C$2),INDIRECT($B23&"!G2:G4500"))
=SUMPRODUCT((MOD(COLUMN(J3:AE3)-3,7)=0)*(J3:AE3)) Every 7th column
=SUMPRODUCT((MOD(COLUMN(J3:IV3)-3,7)=0)*(J3:IV3)) Every 7th column
=SUMPRODUCT((MOD(ROW(A1:A10),2)=0)*(A1:A10)) To find every 2nd row, starting
with row 2 (will add rows 2, 4, 6, 8, and 10):
=SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10)) To find every 2nd row, starting
with row 1 (will add rows 1, 3, 5, 7, and 9):
=SUMPRODUCT((MOD(ROW(A1:A10),3)=0)*(A1:A10)) To find every 3rd row, starting
with row 3 (will add rows 3, 6, and 9): To find every 4th, 5th, 6th, etc...,
starting with row 4, 5, 6, etc..., just change the number 3 in the above formula
to 4, 5, 6, etc.
=SUMPRODUCT((MOD(ROW(A1:A10),3)=1)*(A1:A10)) To find every 3rd row, starting
with row 1 (will add rows 1, 4, 7, and 10): To find every 4th, 5th, 6th, etc...,
starting with row 1, just change the number 3 in the above formula to 4, 5, 6,
etc.
=SUMPRODUCT((MOD(ROW(A1:A97),7)=0)*A1:A97)
=SUMPRODUCT((MOD(ROW(A1:A97),7)=1)*A1:A97)
=SUMPRODUCT((MOD(ROW(Range)-CELL("Row",Range)+H1,H2)=0)*(Range))
=SUMPRODUCT((MONTH(A1:A100)=6)*B1:B100)
=SUMPRODUCT((MONTH(A1:A300)=2)*(ISNUMBER(A1:A300)))
=SUMPRODUCT((MONTH(A1:A500)=1)*(B1:B500))
=SUMPRODUCT((MONTH(A8:A21)=MONTH(G3))*(B8:B21))
=SUMPRODUCT((MONTH(B2:B9)=11)*(A2:A9="Pending"))
=SUMPRODUCT((range1="L")*(LEFT(range2)="B"))
=SUMPRODUCT((Range1=A1)*(Range2=B1)*(Range3=C1))
=SUMPRODUCT((RNGA="A")*(RNGB=1)*RNGC)
=SUMPRODUCT((Sheet2!A1:A100>=Sheet1!A1)*(Sheet2!A1:A100<=DATE(YEAR(Sheet1!A1),MO
NTH(Sheet1!A1)+Sheet1!A2-1,DAY(Sheet1!A1)))*(Sheet2!B1:B100))
=SUMPRODUCT((TEXT(B1:B5,"mmm")="Oct")*A1:A5)
=SUMPRODUCT((YEAR(B2:B9)=2002)*(MONTH(B2:B9)=11)*(A2:A9="Pending"))
=SUMPRODUCT(A1:A3,TRANSPOSE(B2
2)) Array Entered??
=SUMPRODUCT(A2:A5;B2:B5)
=SUMPRODUCT(A2:A56,B2:B56)+SUMPRODUCT(A58:A62,B58:B62)+SUMPRODUCT(A64:A75,B64:B7
5)
=SUMPRODUCT(ABS(A1:A10)*1)
=SUMPRODUCT(COUNTIF(INDIRECT("Week" &(ROW(INDIRECT("1:11")))&"!D4
19"),"CORP"))
=SUMPRODUCT(MOD(COLUMN(I8:IS8),2),I8:IS8)
=SUMPRODUCT(MONTH(B1:B5=9)*A1:A5)
=SUMPRODUCT(N(C2:C765={"B","TB"}))
=SUMPRODUCT(N(EXACT(C2:C765,{"B","TB"})))
=SUMPRODUCT(SUMIF(INDIRECT("'Respondent
NO"&ROW(INDIRECT("1:50"))&"'!C1"),1,INDIRECT("'Respondent
NO"&ROW(INDIRECT("1:50"))&"'!A1")))
=SUMPRODUCT(SUMIF(INDIRECT(ROW(1:35)&"!B1"),TRUE,INDIRECT(ROW(1:35)&"!A5")))
=SUMPRODUCT(x^(ROW(INDIRECT("1:"&CEILING(T,1)))*{1,-1}+T*{0,1}))
=SUMPRODUCT(LARGE(IF(A1:A100="Harry",B1:B100,0),{1,2,3}))
