sumproduct using 'or' logic

  • Thread starter Thread starter Kristina
  • Start date Start date
K

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?
 
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
 
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"),D5:D1000)
 
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"))
 
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?
 
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?
Click to expand...

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
 
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
 
Back
Top