Sumproduct with Multiple Criteria

[JeffTO]

Hi All

I have a question which I cant figure out - I use Sumproduct alot to
sum up multiple criteria and it works well in most situations

I have no problem using multiple criteria for one range:
=SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"})*(C2:C95)) give me
1268 - correct number
=SUMPRODUCT((B2:B95={"01","02","03","04"})*(C2:C95)) give me 1268 -
correct number
=SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"})*(B2:B95={"04"})*(C2:C95))
- give me 1171 - correct number

However.......
=SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"})*(B2:B95={"03","04"})*(C2:C95))
give me "N/A"

and more confusing....
=SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"})*(B2:B95={"01","02","03","04"})*(C2:C95))
- gives me 341 - not even sure where that number comes from

So - can I do what I am attempting to do which is use multiple ranges
with multiple criteria in each range - any help on this would be
greatly appreciated

If you need any other information please let me know

Thanks in advance for your thoughts on this

Jeff



A B C
Qtr5 04 31
Qtr5 03 21
Qtr5 03 2
Qtr5 04 8
Qtr5 02 1
Qtr5 04 38
Qtr5 04 3
Qtr5 04 10
Qtr5 04 13
Qtr5 04 17
Qtr5 04 1
Qtr5 04 19
Qtr5 04 1
Qtr5 04 31
Qtr5 04 3
Qtr5 04 2
Qtr5 04 1
Qtr5 04 67
Qtr5 04 2
Qtr5 04 13
Qtr5 04 2
Qtr5 04 42
Qtr5 04 3
Qtr6 04 26
Qtr6 03 31
Qtr6 04 1
Qtr6 04 1
Qtr6 04 1
Qtr6 04 25
Qtr6 04 1
Qtr6 04 26
Qtr6 04 1
Qtr6 04 17
Qtr6 04 3
Qtr6 04 23
Qtr6 04 2
Qtr6 04 12
Qtr6 04 2
Qtr6 04 6
Qtr6 04 1
Qtr6 04 29
Qtr6 04 1
Qtr6 02 2
Qtr6 02 1
Qtr6 03 2
Qtr6 03 1
Qtr6 04 21
Qtr6 04 1
Qtr6 04 12
Qtr6 04 2
Qtr6 04 30
Qtr6 04 1
Qtr7 04 71
Qtr7 04 1
Qtr7 03 1
Qtr7 04 1
Qtr7 04 13
Qtr7 04 44
Qtr7 04 1
Qtr7 04 12
Qtr7 04 2
Qtr7 03 1
Qtr7 04 10
Qtr7 04 8
Qtr7 04 24
Qtr7 04 48
Qtr7 03 1
Qtr7 04 8
Qtr7 04 1
Qtr7 04 101
Qtr7 04 2
Qtr7 04 13
Qtr7 04 41
Qtr8 04 20
Qtr8 03 28
Qtr8 03 2
Qtr8 04 1
Qtr8 04 11
Qtr8 04 42
Qtr8 04 3
Qtr8 04 15
Qtr8 04 8
Qtr8 04 18
Qtr8 04 1
Qtr8 04 8
Qtr8 04 2
Qtr8 03 1
Qtr8 04 1
Qtr8 04 13
Qtr8 02 1
Qtr8 03 1
Qtr8 04 15
Qtr8 04 29
Qtr8 04 31

 

[Luke M]

Problem is that you are creating multi-column arrays, and multiplying by a
different size multi-column array (use formula auditing to see what I mean).

To accomplish your task, try this:
=SUMPRODUCT(((A2:A7="Qtr8")+(A2:A7="Qtr7")+(A2:A7="Qtr6")+(A2:A7="Qtr5"))*((B2:B7="03")+(B2:B7="04"))*(C2:C7))

 

[T. Valko]

Try it like this...

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A95,{"Qtr8","Qtr7","Qtr6","Qtr5"},0))),--(ISNUMBER(MATCH(B2:B95,{"01","02","03","04"},0))),C2:C95)

Or, better to use cells to hold the criteria...

G2:G5 = Qtr8, Qtr7, Qtr6, Qtr5
H2:H5 = 01, 02, 03, 04

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A95,G2:G5,0))),--(ISNUMBER(MATCH(B2:B95,H2:H5,0))),C2:C95)