|
sumif where cells contain Number & Text
I have a sheet of data, as follows
Row 1 is the dates between now and the end of the year, by day (60 'ish columns) Column A contains a series of names Columns B thru AZ (approx) contain a number and a category (i.e. "3 Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names in column A I want to sum the number of Apples, Oranges and Pears for each day at the bottom of each day. Do I have to split the number & category into separate cells to achieve this ? I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0 |
sumif where cells contain Number & Text
Try this:
................B....................C 1..........Date................Date 2........3 apples...........1 peach 3......10 oranges.........5 pears 4........1 apple............1 pear 5........1 orange..........7 apples A10 = apple A11 = orange A12 = pear Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER) in B10: =SUM(IF(ISNUMBER(SEARCH($A10,B$2:B$5)),--SUBSTITUTE(B$2:B$5, " "&$A10&IF(RIGHT(B$2:B$5)="s","s",""),""))) Copy across then down. Here's a sample file: Sum with text.xls 14kb http://cjoint.com/?lxaTj5QLUh Biff "Fred" wrote in message oups.com... I have a sheet of data, as follows Row 1 is the dates between now and the end of the year, by day (60 'ish columns) Column A contains a series of names Columns B thru AZ (approx) contain a number and a category (i.e. "3 Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names in column A I want to sum the number of Apples, Oranges and Pears for each day at the bottom of each day. Do I have to split the number & category into separate cells to achieve this ? I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0 |
sumif where cells contain Number & Text
Biff,
This works if all cells are properly populated. But if you have apple without a number or 1apple, it failed in my test. So, it appears that all must be a number with a space. Happy Thanksgiving to all from Texas -- Don Guillett SalesAid Software "Biff" wrote in message ... Try this: ...............B....................C 1..........Date................Date 2........3 apples...........1 peach 3......10 oranges.........5 pears 4........1 apple............1 pear 5........1 orange..........7 apples A10 = apple A11 = orange A12 = pear Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER) in B10: =SUM(IF(ISNUMBER(SEARCH($A10,B$2:B$5)),--SUBSTITUTE(B$2:B$5, " "&$A10&IF(RIGHT(B$2:B$5)="s","s",""),""))) Copy across then down. Here's a sample file: Sum with text.xls 14kb http://cjoint.com/?lxaTj5QLUh Biff "Fred" wrote in message oups.com... I have a sheet of data, as follows Row 1 is the dates between now and the end of the year, by day (60 'ish columns) Column A contains a series of names Columns B thru AZ (approx) contain a number and a category (i.e. "3 Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names in column A I want to sum the number of Apples, Oranges and Pears for each day at the bottom of each day. Do I have to split the number & category into separate cells to achieve this ? I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0 |
sumif where cells contain Number & Text
it appears that all must be a number with a space.
Yes, that's correct. That's based on the limited info from the post. Biff "Don Guillett" wrote in message ... Biff, This works if all cells are properly populated. But if you have apple without a number or 1apple, it failed in my test. So, it appears that all must be a number with a space. Happy Thanksgiving to all from Texas -- Don Guillett SalesAid Software "Biff" wrote in message ... Try this: ...............B....................C 1..........Date................Date 2........3 apples...........1 peach 3......10 oranges.........5 pears 4........1 apple............1 pear 5........1 orange..........7 apples A10 = apple A11 = orange A12 = pear Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER) in B10: =SUM(IF(ISNUMBER(SEARCH($A10,B$2:B$5)),--SUBSTITUTE(B$2:B$5, " "&$A10&IF(RIGHT(B$2:B$5)="s","s",""),""))) Copy across then down. Here's a sample file: Sum with text.xls 14kb http://cjoint.com/?lxaTj5QLUh Biff "Fred" wrote in message oups.com... I have a sheet of data, as follows Row 1 is the dates between now and the end of the year, by day (60 'ish columns) Column A contains a series of names Columns B thru AZ (approx) contain a number and a category (i.e. "3 Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names in column A I want to sum the number of Apples, Oranges and Pears for each day at the bottom of each day. Do I have to split the number & category into separate cells to achieve this ? I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0 |
sumif where cells contain Number & Text
Biff/Don Thanks guys for the help, Biff, the formula works a treat, although I don't understand the Substitute action, it's all part of the learning curve. The restrictions outlined were already identified, so not a problem. Thanks again Regards Fred |
sumif where cells contain Number & Text
You're welcome. Thanks for the feedback!
Biff "Fred" wrote in message oups.com... Biff/Don Thanks guys for the help, Biff, the formula works a treat, although I don't understand the Substitute action, it's all part of the learning curve. The restrictions outlined were already identified, so not a problem. Thanks again Regards Fred |
sumif where cells contain Number & Text
I have a similar problem. I have:
A...........B.............C............D..... 2 CE......2 CE........2 A.........2 S... In L:N, I would like a total of all A:J that end in CE, A and S. I tried changing your sum statement, but get at total of 0.08 instead of 4 for CE. "Biff" wrote: it appears that all must be a number with a space. Yes, that's correct. That's based on the limited info from the post. Biff "Don Guillett" wrote in message ... Biff, This works if all cells are properly populated. But if you have apple without a number or 1apple, it failed in my test. So, it appears that all must be a number with a space. Happy Thanksgiving to all from Texas -- Don Guillett SalesAid Software "Biff" wrote in message ... Try this: ...............B....................C 1..........Date................Date 2........3 apples...........1 peach 3......10 oranges.........5 pears 4........1 apple............1 pear 5........1 orange..........7 apples A10 = apple A11 = orange A12 = pear Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER) in B10: =SUM(IF(ISNUMBER(SEARCH($A10,B$2:B$5)),--SUBSTITUTE(B$2:B$5, " "&$A10&IF(RIGHT(B$2:B$5)="s","s",""),""))) Copy across then down. Here's a sample file: Sum with text.xls 14kb http://cjoint.com/?lxaTj5QLUh Biff "Fred" wrote in message oups.com... I have a sheet of data, as follows Row 1 is the dates between now and the end of the year, by day (60 'ish columns) Column A contains a series of names Columns B thru AZ (approx) contain a number and a category (i.e. "3 Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names in column A I want to sum the number of Apples, Oranges and Pears for each day at the bottom of each day. Do I have to split the number & category into separate cells to achieve this ? I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0 |
sumif where cells contain Number & Text
Assume you have:
............L.....M.....N 1........CE....A.....S Enter this formula in L2 as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(IF(ISNUMBER(SEARCH(L1,$A1:$J1)), --SUBSTITUTE($A1:$J1," "&L1,""),"")) Copy across to N2. The results will be (based on your sample): ............L.....M.....N 1........CE....A.....S 2.........4......2......2 Biff "Carole" wrote in message ... I have a similar problem. I have: A...........B.............C............D..... 2 CE......2 CE........2 A.........2 S... In L:N, I would like a total of all A:J that end in CE, A and S. I tried changing your sum statement, but get at total of 0.08 instead of 4 for CE. "Biff" wrote: it appears that all must be a number with a space. Yes, that's correct. That's based on the limited info from the post. Biff "Don Guillett" wrote in message ... Biff, This works if all cells are properly populated. But if you have apple without a number or 1apple, it failed in my test. So, it appears that all must be a number with a space. Happy Thanksgiving to all from Texas -- Don Guillett SalesAid Software "Biff" wrote in message ... Try this: ...............B....................C 1..........Date................Date 2........3 apples...........1 peach 3......10 oranges.........5 pears 4........1 apple............1 pear 5........1 orange..........7 apples A10 = apple A11 = orange A12 = pear Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER) in B10: =SUM(IF(ISNUMBER(SEARCH($A10,B$2:B$5)),--SUBSTITUTE(B$2:B$5, " "&$A10&IF(RIGHT(B$2:B$5)="s","s",""),""))) Copy across then down. Here's a sample file: Sum with text.xls 14kb http://cjoint.com/?lxaTj5QLUh Biff "Fred" wrote in message oups.com... I have a sheet of data, as follows Row 1 is the dates between now and the end of the year, by day (60 'ish columns) Column A contains a series of names Columns B thru AZ (approx) contain a number and a category (i.e. "3 Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names in column A I want to sum the number of Apples, Oranges and Pears for each day at the bottom of each day. Do I have to split the number & category into separate cells to achieve this ? I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0 |
sumif where cells contain Number & Text
Thanks! I figured it out while waiting for a response. I had an "a" instead
of "A". The following allows me to search for the cells without having a seperate cell identifying the criteria. =SUM(IF(ISNUMBER(SEARCH("A",$A4:$J4)), --SUBSTITUTE($A4:$J4," "&IF(RIGHT($A4:$J4,1)="A","A",""),""))) "T. Valko" wrote: Assume you have: ............L.....M.....N 1........CE....A.....S Enter this formula in L2 as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(IF(ISNUMBER(SEARCH(L1,$A1:$J1)), --SUBSTITUTE($A1:$J1," "&L1,""),"")) Copy across to N2. The results will be (based on your sample): ............L.....M.....N 1........CE....A.....S 2.........4......2......2 Biff "Carole" wrote in message ... I have a similar problem. I have: A...........B.............C............D..... 2 CE......2 CE........2 A.........2 S... In L:N, I would like a total of all A:J that end in CE, A and S. I tried changing your sum statement, but get at total of 0.08 instead of 4 for CE. "Biff" wrote: it appears that all must be a number with a space. Yes, that's correct. That's based on the limited info from the post. Biff "Don Guillett" wrote in message ... Biff, This works if all cells are properly populated. But if you have apple without a number or 1apple, it failed in my test. So, it appears that all must be a number with a space. Happy Thanksgiving to all from Texas -- Don Guillett SalesAid Software "Biff" wrote in message ... Try this: ...............B....................C 1..........Date................Date 2........3 apples...........1 peach 3......10 oranges.........5 pears 4........1 apple............1 pear 5........1 orange..........7 apples A10 = apple A11 = orange A12 = pear Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER) in B10: =SUM(IF(ISNUMBER(SEARCH($A10,B$2:B$5)),--SUBSTITUTE(B$2:B$5, " "&$A10&IF(RIGHT(B$2:B$5)="s","s",""),""))) Copy across then down. Here's a sample file: Sum with text.xls 14kb http://cjoint.com/?lxaTj5QLUh Biff "Fred" wrote in message oups.com... I have a sheet of data, as follows Row 1 is the dates between now and the end of the year, by day (60 'ish columns) Column A contains a series of names Columns B thru AZ (approx) contain a number and a category (i.e. "3 Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names in column A I want to sum the number of Apples, Oranges and Pears for each day at the bottom of each day. Do I have to split the number & category into separate cells to achieve this ? I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0 |
sumif where cells contain Number & Text
You're welcome. Thanks for the feedback!
Biff "Carole" wrote in message ... Thanks! I figured it out while waiting for a response. I had an "a" instead of "A". The following allows me to search for the cells without having a seperate cell identifying the criteria. =SUM(IF(ISNUMBER(SEARCH("A",$A4:$J4)), --SUBSTITUTE($A4:$J4," "&IF(RIGHT($A4:$J4,1)="A","A",""),""))) "T. Valko" wrote: Assume you have: ............L.....M.....N 1........CE....A.....S Enter this formula in L2 as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(IF(ISNUMBER(SEARCH(L1,$A1:$J1)), --SUBSTITUTE($A1:$J1," "&L1,""),"")) Copy across to N2. The results will be (based on your sample): ............L.....M.....N 1........CE....A.....S 2.........4......2......2 Biff "Carole" wrote in message ... I have a similar problem. I have: A...........B.............C............D..... 2 CE......2 CE........2 A.........2 S... In L:N, I would like a total of all A:J that end in CE, A and S. I tried changing your sum statement, but get at total of 0.08 instead of 4 for CE. "Biff" wrote: it appears that all must be a number with a space. Yes, that's correct. That's based on the limited info from the post. Biff "Don Guillett" wrote in message ... Biff, This works if all cells are properly populated. But if you have apple without a number or 1apple, it failed in my test. So, it appears that all must be a number with a space. Happy Thanksgiving to all from Texas -- Don Guillett SalesAid Software "Biff" wrote in message ... Try this: ...............B....................C 1..........Date................Date 2........3 apples...........1 peach 3......10 oranges.........5 pears 4........1 apple............1 pear 5........1 orange..........7 apples A10 = apple A11 = orange A12 = pear Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER) in B10: =SUM(IF(ISNUMBER(SEARCH($A10,B$2:B$5)),--SUBSTITUTE(B$2:B$5, " "&$A10&IF(RIGHT(B$2:B$5)="s","s",""),""))) Copy across then down. Here's a sample file: Sum with text.xls 14kb http://cjoint.com/?lxaTj5QLUh Biff "Fred" wrote in message oups.com... I have a sheet of data, as follows Row 1 is the dates between now and the end of the year, by day (60 'ish columns) Column A contains a series of names Columns B thru AZ (approx) contain a number and a category (i.e. "3 Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names in column A I want to sum the number of Apples, Oranges and Pears for each day at the bottom of each day. Do I have to split the number & category into separate cells to achieve this ? I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0 |
| All times are GMT +1. The time now is 07:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com