How to write an Or statement inside Sumproduct?

[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.

 

[T. Valko]

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"),D110)

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),D110)

 

[Bassman62]

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.

 

[Bassman62]

Biff,
Thanks very much for the prompt reply.(My own attempt was getting quite
long.)
Thanks again.

 

[T. Valko]

You're welcome. Thanks for the feedback!