How to write an Or statement inside Sumproduct?

  • Thread starter Thread starter Bassman62
  • Start date Start date
B

Bassman62

Using xl-2007
I reallize that the OR statement cannot be used as an array in a Sumproduct.
In this case how can I sum the values in column D or count the rows where
columns A & B = t and column C = x Or y?

A B C D
1 t t x 10
2 t f x 5
3 t t y 10

Thanks.
 
Try these...

For the count:

=SUMPRODUCT(--(A1:A10="T"),--(B1:B10="T"),(C1:C10="X")+(C1:C10="Y"))

For the sum:

=SUMPRODUCT(--(A1:A10="T"),--(B1:B10="T"),(C1:C10="X")+(C1:C10="Y"),D1:D10)

Better to use cells to hold the criteria:

F1 = T
F2 = X
F3 = Y

=SUMPRODUCT(--(A1:A10=F1),--(B1:B10=F1),(C1:C10=F2)+(C1:C10=F3))

=SUMPRODUCT(--(A1:A10=F1),--(B1:B10=F1),(C1:C10=F2)+(C1:C10=F3),D1:D10)
 
Bernard and Bernie,
Thank you very much. This answers my question.
I now see how adding the arrays (C...="x")+(C...="y") will return 1 when
C...= "x" OR "y".
Thanks again.
 
Biff,
Thanks very much for the prompt reply.(My own attempt was getting quite
long.)
Thanks again.
 
Back
Top