| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Multiple Criteria
Hello Everyone,
I have been try to learn how to use the SUM function with multiple criteria. I have run into a road block with my experimentation. By the way, I am using Excel 2003 SP3. My data looks like the following: city Mfr model volume dearborn ford taurus 10 troy ford fusion 20 shelby gm lacrosse 10 warren chrysler sebring 10 dearborn gm camaro 30 detroit ford fusion 40 shelby chrysler magnum 20 warren chrysler avenger 40 troy chrysler avenger 20 detroit gm lacrosse 50 detroit gm impala 30 warren ford fusion 40 troy chrysler avenger 20 detroit ford taurus 10 The formula I am having a problem with is: {=SUM(($C$5:$C$18="taurus")+($C$5:$C$18="fusion")+ ($D$5:$D$18))} The Sumprduct yields 120 as the answer which is correct but the formula above yields 355 as the answer. What am I missing? Thanx in advance, John |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Multiple Criteria
'array entered
=SUM((ISNUMBER(SEARCH({"taurus","fusion"},$C$5:$C$ 18)))*$D$5:$D$18) OR =SUMPRODUCT((ISNUMBER(SEARCH({"taurus","fusion"}, $C$5:$C$18)))*$D$5:$D$18) If this post helps click Yes --------------- Jacob Skaria "JCS" wrote: Hello Everyone, I have been try to learn how to use the SUM function with multiple criteria. I have run into a road block with my experimentation. By the way, I am using Excel 2003 SP3. My data looks like the following: city Mfr model volume dearborn ford taurus 10 troy ford fusion 20 shelby gm lacrosse 10 warren chrysler sebring 10 dearborn gm camaro 30 detroit ford fusion 40 shelby chrysler magnum 20 warren chrysler avenger 40 troy chrysler avenger 20 detroit gm lacrosse 50 detroit gm impala 30 warren ford fusion 40 troy chrysler avenger 20 detroit ford taurus 10 The formula I am having a problem with is: {=SUM(($C$5:$C$18="taurus")+($C$5:$C$18="fusion")+ ($D$5:$D$18))} The Sumprduct yields 120 as the answer which is correct but the formula above yields 355 as the answer. What am I missing? Thanx in advance, John |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Multiple Criteria
Thank you Jacob,
So, according to your solution, my solution is not possible. John "Jacob Skaria" wrote: 'array entered =SUM((ISNUMBER(SEARCH({"taurus","fusion"},$C$5:$C$ 18)))*$D$5:$D$18) OR =SUMPRODUCT((ISNUMBER(SEARCH({"taurus","fusion"}, $C$5:$C$18)))*$D$5:$D$18) If this post helps click Yes --------------- Jacob Skaria "JCS" wrote: Hello Everyone, I have been try to learn how to use the SUM function with multiple criteria. I have run into a road block with my experimentation. By the way, I am using Excel 2003 SP3. My data looks like the following: city Mfr model volume dearborn ford taurus 10 troy ford fusion 20 shelby gm lacrosse 10 warren chrysler sebring 10 dearborn gm camaro 30 detroit ford fusion 40 shelby chrysler magnum 20 warren chrysler avenger 40 troy chrysler avenger 20 detroit gm lacrosse 50 detroit gm impala 30 warren ford fusion 40 troy chrysler avenger 20 detroit ford taurus 10 The formula I am having a problem with is: {=SUM(($C$5:$C$18="taurus")+($C$5:$C$18="fusion")+ ($D$5:$D$18))} The Sumprduct yields 120 as the answer which is correct but the formula above yields 355 as the answer. What am I missing? Thanx in advance, John |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Multiple Criteria
What your formula does is
=sum(count number of 'taurus'+ count number of 'fusion' + the array D5:D18) =sum(2+3+350) =355 If this post helps click Yes --------------- Jacob Skaria "JCS" wrote: Thank you Jacob, So, according to your solution, my solution is not possible. John "Jacob Skaria" wrote: 'array entered =SUM((ISNUMBER(SEARCH({"taurus","fusion"},$C$5:$C$ 18)))*$D$5:$D$18) OR =SUMPRODUCT((ISNUMBER(SEARCH({"taurus","fusion"}, $C$5:$C$18)))*$D$5:$D$18) If this post helps click Yes --------------- Jacob Skaria "JCS" wrote: Hello Everyone, I have been try to learn how to use the SUM function with multiple criteria. I have run into a road block with my experimentation. By the way, I am using Excel 2003 SP3. My data looks like the following: city Mfr model volume dearborn ford taurus 10 troy ford fusion 20 shelby gm lacrosse 10 warren chrysler sebring 10 dearborn gm camaro 30 detroit ford fusion 40 shelby chrysler magnum 20 warren chrysler avenger 40 troy chrysler avenger 20 detroit gm lacrosse 50 detroit gm impala 30 warren ford fusion 40 troy chrysler avenger 20 detroit ford taurus 10 The formula I am having a problem with is: {=SUM(($C$5:$C$18="taurus")+($C$5:$C$18="fusion")+ ($D$5:$D$18))} The Sumprduct yields 120 as the answer which is correct but the formula above yields 355 as the answer. What am I missing? Thanx in advance, John |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Multiple Criteria
Thanks again Jacob,
You gave me exactly what i was looking for. I was looking for the why my formula was not working. Thank you so much! John "Jacob Skaria" wrote: What your formula does is =sum(count number of 'taurus'+ count number of 'fusion' + the array D5:D18) =sum(2+3+350) =355 If this post helps click Yes --------------- Jacob Skaria "JCS" wrote: Thank you Jacob, So, according to your solution, my solution is not possible. John "Jacob Skaria" wrote: 'array entered =SUM((ISNUMBER(SEARCH({"taurus","fusion"},$C$5:$C$ 18)))*$D$5:$D$18) OR =SUMPRODUCT((ISNUMBER(SEARCH({"taurus","fusion"}, $C$5:$C$18)))*$D$5:$D$18) If this post helps click Yes --------------- Jacob Skaria "JCS" wrote: Hello Everyone, I have been try to learn how to use the SUM function with multiple criteria. I have run into a road block with my experimentation. By the way, I am using Excel 2003 SP3. My data looks like the following: city Mfr model volume dearborn ford taurus 10 troy ford fusion 20 shelby gm lacrosse 10 warren chrysler sebring 10 dearborn gm camaro 30 detroit ford fusion 40 shelby chrysler magnum 20 warren chrysler avenger 40 troy chrysler avenger 20 detroit gm lacrosse 50 detroit gm impala 30 warren ford fusion 40 troy chrysler avenger 20 detroit ford taurus 10 The formula I am having a problem with is: {=SUM(($C$5:$C$18="taurus")+($C$5:$C$18="fusion")+ ($D$5:$D$18))} The Sumprduct yields 120 as the answer which is correct but the formula above yields 355 as the answer. What am I missing? Thanx in advance, John |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Multiple Criteria
On Nov 18, 10:55*pm, Jacob Skaria
wrote: What your formula does is =sum(count number of 'taurus'+ count number of 'fusion' + the array D5:D18) =sum(2+3+350) =355 If this post helps click Yes --------------- Jacob Skaria "JCS" wrote: Thank you Jacob, So, according to your solution, my solution is not possible. John "Jacob Skaria" wrote: 'array entered =SUM((ISNUMBER(SEARCH({"taurus","fusion"},$C$5:$C$ 18)))*$D$5:$D$18) OR =SUMPRODUCT((ISNUMBER(SEARCH({"taurus","fusion"}, $C$5:$C$18)))*$D$5:$D$18) If this post helps click Yes --------------- Jacob Skaria "JCS" wrote: Hello Everyone, I have been try to learn how to use the SUM function with multiple criteria. *I have run into a road block with my experimentation. *By the way, I am using Excel 2003 SP3. *My data looks like the following: city * * Mfr * * model * volume dearborn ford * *taurus *10 troy * * ford * *fusion *20 shelby * gm * * *lacrosse * * * *10 warren * chrysler * * * *sebring 10 dearborn gm * * *camaro *30 detroit *ford * *fusion *40 shelby * chrysler * * * *magnum *20 warren * chrysler * * * *avenger 40 troy * * chrysler * * * *avenger 20 detroit *gm * * *lacrosse * * * *50 detroit *gm * * *impala *30 warren * ford * *fusion *40 troy * * chrysler * * * *avenger 20 detroit *ford * *taurus *10 The formula I am having a problem with is: {=SUM(($C$5:$C$18="taurus")+($C$5:$C$18="fusion")+ ($D$5:$D$18))} The Sumprduct yields 120 as the answer which is correct but the formula above yields 355 as the answer. *What am I missing? Thanx in advance, John Simple way is =SUMIF(C5:C18,"taurus",D5:D18)+SUMIF(C5:C18,"fusio n",D5:D18) Worawat |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Multiple Criteria
Many thannks Don. Your help is greatly appreciated.
John "Don Guillett" wrote: =SUMPRODUCT((C5:C18={"taurus","fusion"})*D5:D18) Need NOT be array entered -- Don Guillett Microsoft MVP Excel SalesAid Software "JCS" wrote in message ... Hello Everyone, I have been try to learn how to use the SUM function with multiple criteria. I have run into a road block with my experimentation. By the way, I am using Excel 2003 SP3. My data looks like the following: city Mfr model volume dearborn ford taurus 10 troy ford fusion 20 shelby gm lacrosse 10 warren chrysler sebring 10 dearborn gm camaro 30 detroit ford fusion 40 shelby chrysler magnum 20 warren chrysler avenger 40 troy chrysler avenger 20 detroit gm lacrosse 50 detroit gm impala 30 warren ford fusion 40 troy chrysler avenger 20 detroit ford taurus 10 The formula I am having a problem with is: {=SUM(($C$5:$C$18="taurus")+($C$5:$C$18="fusion")+ ($D$5:$D$18))} The Sumprduct yields 120 as the answer which is correct but the formula above yields 355 as the answer. What am I missing? Thanx in advance, John . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
| match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
