Posted to microsoft.public.excel.worksheet.functions
|
|
Sum & two column conditions
Ragdyer, thanks so much, very helpful!
"Ragdyer" wrote:
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
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Paul Mathews" wrote in message
...
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.
"Peo Sjoblom" wrote:
=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
"Mikael L" wrote in message
...
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.
|