Sumproduct using OR

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

 

[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

 

[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})*(

 

[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

 

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