How to write an Or statement inside Sumproduct?
 

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.

How to write an Or statement inside Sumproduct?
 

=sumproduct(--(A1:A100="t"),--(B1:B100="t"),--(or((C1:C100="x"),(D1:D100="y"))))

"Bassman62" wrote:

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.

How to write an Or statement inside Sumproduct?
 

=sumproduct(--(A1:A100="t"),--(B1:B100="t"),--(or((C1:C100="x"),(D1:D100="y"))))

"Bassman62" wrote:

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.

How to write an Or statement inside Sumproduct?
 

Bassman,

=SUMPRODUCT((A1:A10="t")*(B1:B10="t")*((C1:C10="x" )+(C1:C10="y"))*D1:D10)

HTH,
Bernie
MS Excel MVP


"Bassman62" wrote in message
...
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.

How to write an Or statement inside Sumproduct?
 

To sum D when both A and B equal 't' and C is either "x" or "y"
=SUMPRODUCT((A1:A20="t")*(B1:B20="t")*((C1:C10="x" )*+(C1:C10="y"))*(D1*D10))

Think of a test (is A1= "t") as giving an answer TRUE or FALSE but Excel
will treat as these 1 or 0 when we do math on them.

A test B test product of A*B tests
1 1 1
0 1 0
1 0 0
0 0 0
So multiplication is the same as AND

C = x test C = y test addition of two tests
1 1 1
0 1 1
1 0 1
0 0 0
So addition is the same as OR

To count rather than sum;
=SUMPRODUCT((A1:A20="t")*(B1:B20="t")*((C1:C10="x" )+(C1:C10="y")))

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Bassman62" wrote in message
...
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.

How to write an Or statement inside Sumproduct?
 

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)

--
Biff
Microsoft Excel MVP


"Bassman62" wrote in message
...
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.

How to write an Or statement inside Sumproduct?
 

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.

"Bernard Liengme" wrote in message
...
To sum D when both A and B equal 't' and C is either "x" or "y"
=SUMPRODUCT((A1:A20="t")*(B1:B20="t")*((C1:C10="x" )*+(C1:C10="y"))*(D1*D10))

Think of a test (is A1= "t") as giving an answer TRUE or FALSE but Excel
will treat as these 1 or 0 when we do math on them.

A test B test product of A*B tests
1 1 1
0 1 0
1 0 0
0 0 0
So multiplication is the same as AND

C = x test C = y test addition of two tests
1 1 1
0 1 1
1 0 1
0 0 0
So addition is the same as OR

To count rather than sum;
=SUMPRODUCT((A1:A20="t")*(B1:B20="t")*((C1:C10="x" )+(C1:C10="y")))

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Bassman62" wrote in message
...
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.

How to write an Or statement inside Sumproduct?
 

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


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Bassman62" wrote in message
...
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.

How to write an Or statement inside Sumproduct?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Bassman62" wrote in message
...
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.