Sumproduct

Y

Yul

Hello!

I have the following problem:
A B C D
1 Name Region Size Class Sales
2 Joe A 1 100
3 Bill B 2 50
4 John C 3 25
5 Joe A 2 75

I want to use the sumproduct formula to calculate the salestotal for
Joe in Region A in SizeClass 1 or 2. I don't know how to combine these
three conditions in one formula! The answer should be 175!

Thanks for advise!

/Yul
 
B

Bob Phillips

Hi Yul,

SUMPRODUCT as ever.

=SUMPRODUCT((A1:A5="Joe")*(B1:B5="A")*((C1:C5=1)+(C1:C5=2)),D1:D5)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
F

Frank Kabel

Hi
change Bob's formula as follows:
=SUMPRODUCT((A1:A5="Joe")*(B1:B5="A")*((C1:C5="1")+(C1:C5="2")),D1:D5)

HTH
Frank
 
H

Harlan Grove

change Bob's formula as follows:
=SUMPRODUCT((A1:A5="Joe")*(B1:B5="A")*((C1:C5="1")+(C1:C5="2")),D1:D5)
...

Or, since the col C conditions are mutually exclusive,

=SUMPRODUCT((A1:A5="Joe")*(B1:B5="A")*((C1:C5={"1","2"}))*D1:D5)
 
F

Frank Kabel

Harlan said:
=SUMPRODUCT((A1:A5="Joe")*(B1:B5="A")*((C1:C5={"1","2"}))*D1:D5)


Thanks Harlan this is even better! Though I always struggle with the
{"1","2"} part in my non-english version...
Regards
Frank
 
F

Frank Kabel

Peo said:
Hi Frank,

in Sweden we use ={"1"\"2"}

Peo
in Germany {"1"."2"}
but I only got this information after Bob send me an example worksheet
and Excel did the conversion...
Frank
 

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