Sumproduct with And?
 

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

Sumproduct with And?
 

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)),Ma ster!$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)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"SteveC" wrote in message
...
=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!

Sumproduct with And?
 

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.

"SteveC" wrote:

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

Sumproduct with And?
 

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

"SteveC" wrote:

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

Sumproduct with And?
 

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))*(Ma ster!$B$7:$B$467))

the latter entered with ctrl + shift & enter


Regards,

Peo Sjoblom

"SteveC" wrote:

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

Sumproduct with And?
 

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

"Bernard Liengme" wrote:

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)),Ma ster!$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)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"SteveC" wrote in message
...
=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!

Sumproduct with And?
 

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!

Sumproduct with And?
 

Try...

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

Hope this helps!

In article ,
SteveC wrote:

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

"Bernard Liengme" wrote:

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)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"SteveC" wrote in message
...
=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!