|
SumProduct - Examples of Syntax
Miscellaneous examples pulled from questions in the Microsoft Excel Newsgroups - Just ti give an idea of how other functions would be nested inside this particular function
=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:D100))
=SUMPRODUCT((A1:A100>=$E$1)*(A1:A100<=$E$2)*(B1:B100=$E$3)*(C1:C100=$E$4)*D1:D100)
=SUMPRODUCT((A1:A100>=1)*(A1:A100<=100)*A1:A100)SUMPRODUCT((A1:A100>=1)*(A1:A100<=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=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="Week1")*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),INDI RECT($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
|
