Sumproduct using OR

S

Steve H

Hello
I am trying to use a sumproduct formula to evaluate the following:

if Range1=Criteria1 or =Criteria2
and Range2=Criteria3 or =Criteria4
and Range3=Criteria5
and Range4=Criteria6
return the value in SumRange

so my formula would look like this?

sumproduct((Range1=Criteria1)+(Range1=Criteria2)*(Range2=Criteria3)+
(Range2=Criteria4)
*(Range3=Criteria5)*(Range4=Criteria6 )* SumRange)

Thanks!
 
P

Pete_UK

You need to put a few more brackets in, like this:

=sumproduct(((Range1=Criteria1)+(Range1=Criteria2)) *
((Range2=Criteria3)+(Range2=Criteria4)) * (Range3=Criteria5) *
(Range4=Criteria6) * SumRange)

I've put a few spaces in so hopefully the long formula will wrap at
sensible places.

Hope this helps.

Pete
 
D

Don Guillett Excel MVP

Hello
I am trying to use a sumproduct formula to evaluate the following:

if Range1=Criteria1 or =Criteria2
and Range2=Criteria3 or =Criteria4
and Range3=Criteria5
and Range4=Criteria6
return the value in SumRange

so my formula would look like this?

sumproduct((Range1=Criteria1)+(Range1=Criteria2)*(Range2=Criteria3)+
(Range2=Criteria4)
*(Range3=Criteria5)*(Range4=Criteria6 )* SumRange)

Thanks!

Range1=({Criteria1,Criteria2})*(
 
S

Steve H

SUMPRODUCT((Range1=({Criteria1,Criteria2}))*(Range2=({Criteria3,Criteria4}))*(Range3=Criteria5)*(Range4=Criteria6)*(SumRange))

Gives me #N/A

Where Pete's returns the expected result.

I like the Range1=({Criteria1,Criteria2}) solution better.
Criteria1-4 are text values so would it look like
Range1=({"CAT","DOG"}))*(Range2=({"SNAKE","FISH"})


I'm guessing it may be in some missing or extra parentheses somewhere
Thanks
 
D

Don Guillett Excel MVP

SUMPRODUCT((Range1=({Criteria1,Criteria2}))*(Range2=({Criteria3,Criteria4})­)*(Range3=Criteria5)*(Range4=Criteria6)*(SumRange))

Gives me #N/A

Where Pete's returns the expected result.

I like the Range1=({Criteria1,Criteria2}) solution better.
Criteria1-4 are text values so would it look like
Range1=({"CAT","DOG"}))*(Range2=({"SNAKE","FISH"})

I'm guessing it may be in some missing or extra parentheses somewhere
Thanks
An actual example with correct syntax
=-
SUMPRODUCT((TRIM(ChecksC)={"electricity","water","sewer","garbage"})*(ChecksD))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top