Sumproduct with And?

[Guest]

=SUMPRODUCT(--(Master!$A$7:$A$467=B5),Master!$B$7:$B$467)

that works. But:

=SUMPRODUCT(--(Master!$A$7:$A$467=or(B5,b6,b7,b8)),Master!$B$7:$B$467)

doesn't.

Basically, I'm trying to add together numbers that match any label in b5:b8

To further explain, and not to confuse the mattter:
so if b5:b8 include apples, bannansas, pears, pineapples, then I can have
sum all these and label this new cell "Fruit"

any suggestions? thanks very much!

 

[Bernard Liengme]

Try
=SUMPRODUCT(--(Master!$A$7:$A$467=B5:B8),Master!$B$7:$B$467)

OR
=SUMPRODUCT(--((Master!$A$7:$A$467=B5)+(Master!$A$7:$A$467=B6)+(Master!$A$7:$A$467=B7)+(Master!$A$7:$A$467=B8)),Master!$B$7:$B$467)

OR
Select B5:B8 and use Insert|Name|Define and give it a name such as MyList
then use
=SUMPRODUCT(--(Master!$A$7:$A$467=myList),Master!$B$7:$B$467)

 

[Guest]

You may try this one:
=SUMPRODUCT((Master!$A$7:$A$467=B5)+(Master!$A$7:$A$467=B6)+(Master!$A$7:$A$467=B7)+(Master!$A$7:$A$467=B8),Master!$B$7:$B$467)
This will add the four different arrays from each condition on a single
array, that is what SUMPRODUCT accepts as the entry.
The formula OR checks for TRUE or FALSE in its parameters list, and returns
TRUE or FALSE, that it is not what you need in this case.

Hope this helps,
Miguel.

 

[Guest]

=SUMPRODUCT(--NOT(ISERROR(MATCH(Master!$A$7:$A$467,B5:B8,0))),Master!$B$7:$B$467)

 

[Guest]

A couple of ways


=SUMPRODUCT(--(ISNUMBER(MATCH(Master!$A$7:$A$467,$B$5:$B$8,0))),Master!$B$7:$B$467)

or

=SUM((Master!$A$7:$A$467=TRANSPOSE($B$5:$B$8))*(Master!$B$7:$B$467))

the latter entered with ctrl + shift & enter


Regards,

Peo Sjoblom

 

[Guest]

Hey thanks,

I get a #N/A error when I try your 1 and 3 methods... the 2nd one works of
course, but it's kind of long...

 

[Guest]

Miguel, Bernard, Bob, Peo, thanks for all your help... while using multiple +
signs works, Bob's or Peo's are the most efficient... thanks very much!

 

[Domenic]

Try...

=SUMPRODUCT(--ISNUMBER(MATCH(Master!$A$7:$A$467,B5:B8,0)),Master!$B$7:$B$
467)

Hope this helps!