Sum & two column conditions

[Guest]

I have a sheet (Excel 2003) containing three columns where a condition in the
first column A should get a sum of values from column B and a count of values
from column C. But I do not know how to do it.

A B C
TS 10 p
TS 20 c
TS 30 c
TF 10 p
TF 10 p
D 30 c
N 20 c

What I want is to be able to sum col B if col A is e.g TS and col C is p
(answer 10), then A=TS and C=c (answer 50). I then want to count the number
of p if e.g. A=TS.

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A200="TS"),--(C2:C200="p"),B2:B200)

or with more flexibility

=SUMPRODUCT(--(A2:A200=E1),--(C2:C200=F1),B2:B200)

where E1 would hold the criteria in A and F1 the criteria in C, that way you
won't have to edit the formula when changing criteria



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

Peo, that is very cool. I would have applied an array formula but you've
managed this solution with a regular SumProduct. Out of curiosity what does
the "--" signify in the SumProduct? Haven't seen this before in any of my
readings. Thanks in advance.

 

[Ragdyer]

And, for your Count question, try:

=Sumproduct((A1:A100="TS")*(C1:C100="p"))

Or, as Peo suggested, use specific cells to contain the criteria for your
formulas;
With Column A criteria in D1,
And Column C criteria in D2, try:

=SUMPRODUCT((A1:A100=D1)*(C1:C100=D2))

 

[Ragdyer]

Check out these 2 web pages for a concise explanation and and very detailed
explanation:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

And

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Ragdyer, thanks so much, very helpful!

Check out these 2 web pages for a concise explanation and and very detailed
explanation:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

And

http://www.xldynamic.com/source/xld.SUMPRODUCT.html