sumproduct using 'or' logic

[Kristina]

Hello,

The formula that I'm trying to write should return a
total (sum) where the criteria met are evaluated
using 'or' and 'and' logic. Sumproduct seems to be based
on 'and' logic so I am wondering how to
incorporate 'or'...

Specifically, I want the sum for when (a5:a1000="Y" OR
b5:b1000="Y") AND c5:c1000="Y".

I tried to nest an IF(OR statement into the sumproduct
formula and that didn't work, nor can I get Countif to do
what I want.

Does anyone have any suggestions?

 

[JulieD]

Hi Kristina

Not actually sure what you're summing but this should get you started

=SUMPRODUCT(((A5:A1000="Y")+(B5:B1000="Y"))*(C5:C1000="Y"))

as i understand it you can use the + to indicate an OR in the SUMPRODUCT()

Cheers
Julie

 

[Don Guillett]

This will count all y's in the range
=SUMPRODUCT((A5:C10="Y")*1)

 

[Frank Kabel]

Hi Kristina
if you want to sum column D (you haven't stated that) try the following
=SUMPRODUCT(((A5:A1000="Y")+(B5:B1000="Y")>0)*(C5:C1000="Y"),D51000)

 

[Frank Kabel]

Hi Julie
note this will give wrong results if both criteria (in column A and
column B) are met
Reason:
=SUMPRODUCT(((A5:A1000="Y")+(B5:B1000="Y"))*(C5:C1000="Y"))
will evaluate for this row to
SUMPRODUCT(((1)+(1))*(C condition))

So add the following:
=SUMPRODUCT(((A5:A1000="Y")+(B5:B1000="Y")>0)*(C5:C1000="Y"))

 

[JulieD]

Hi Frank

yep, didn't spot it was doubling the value.

thanks
JulieD

 

[Kristina]

this might result in double counting...

what I want to know is if either cell in the same row of
column a or b equals 'Y' and if cell in same row of
column c equals 'Y' then count once.
col a col b col c sumproduct for row
row 1 y n y 1
row 2 n n y 0
row 3 n y y 1
row 4 y y y 1 (not 2!)
row 5 y y n 0
row 6 y n n 0
row 7 n y n 0
row 8 n n n 0

actual sumproduct result = 3
if I specify the range as a1:c8 then the formula is not
evaluating the result in col a or b, but rather returning
all instances of y in those columns resulting in a final
calculation of 4.

Is the only way to do this is to create another
intermediary column that has an IF(OR(a1="Y",b1="Y"),1,O)
then use that new column as my criteria in sumproduct and
combine it with column c?

 

[Frank Kabel]

Hi Kristina
see your other replies for solutions

 

[Guest]

Thanks, Frank. I will.

-----Original Message-----
Hi Kristina
see your other replies for solutions

--
Regards
Frank Kabel
Frankfurt, Germany

.

 

[Don Guillett]

mis read >see Frank's

 

[Ron Rosenfeld]

Hello,

The formula that I'm trying to write should return a
total (sum) where the criteria met are evaluated
using 'or' and 'and' logic. Sumproduct seems to be based
on 'and' logic so I am wondering how to
incorporate 'or'...

Specifically, I want the sum for when (a5:a1000="Y" OR
b5:b1000="Y") AND c5:c1000="Y".

I tried to nest an IF(OR statement into the sumproduct
formula and that didn't work, nor can I get Countif to do
what I want.

Does anyone have any suggestions?

An array formula can do that easily:

=SUM(((A1:A8="y")+(B1:B8="y")>0)*(C1:C8="y"))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron

 

[Kalabalana]

is it just me or did no one solve her problem? I have a nested i
statement that will solve the problem, but it looks like frank ha
solved it from what everyone is saying, can someone post the solutio
again

 

[Don Guillett]

No idea what you are talking about. Around here we stay in the original
thread.

 

[Debra Dalgleish]

To find postings, you can search the Google archives:


http://groups.google.com/groups?&threadm=#[email protected]

 

[Don Guillett]

Just trying to get the poster to do it right.