Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum & two column conditions
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum & two column conditions
=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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum & two column conditions
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum & two column conditions
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)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum & two column conditions
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. |
#6
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Formula to Extract value on 3 column based on two conditions | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Add items in column 3 if column 1 and 2 conditions are met | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |