Hi Terry
find below a repost from Ken Wright:
---------------
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&"!F
2: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(Shee
t1!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:A
75,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)&"!A
5")))
=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}))
