Sumproduct with And?

S

SteveC

=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!
 
B

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

Miguel Zapico

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.
 
B

Bob Umlas, Excel MVP

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

Peo Sjoblom

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
 
S

SteveC

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...
 
S

SteveC

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

Domenic

Try...

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

Hope this helps!
 
Top