Sumproduct

  • Thread starter Thread starter Yul
  • Start date Start date
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
 
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)
 
Hi
change Bob's formula as follows:
=SUMPRODUCT((A1:A5="Joe")*(B1:B5="A")*((C1:C5="1")+(C1:C5="2")),D1:D5)

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

Or, since the col C conditions are mutually exclusive,

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


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

in Sweden we use ={"1"\"2"}
Click to expand...

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