| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003, SUM left-most characters
I have a range of cells with these (and only these) possible values:
"1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
One way:
=SUMPRODUCT(SUBSTITUTE(SUBSTITUTE(A1:A5,"Completel y Inadequate",""),"Completely Adequate","")+0) Adapt the range to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "David Aukerman" wrote: I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
Maybe this:
=SUMPRODUCT(--(LEFT(A1:A5&0))) I'm assuming the cells don't actually contain the quotes. -- Biff Microsoft Excel MVP "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
Max,
Thanks for the quick reply. I like that solution. But now I have a deeper question: can I do this same thing in a SUMIF? E.g., =SUMIF(A1:A100,"Criterion1",B1:B100) has trouble if the B column contains any "1 Completely Inadequate" and "5 Completely Adequate" entries. Any ideas about this? --David "Max" wrote: One way: =SUMPRODUCT(SUBSTITUTE(SUBSTITUTE(A1:A5,"Completel y Inadequate",""),"Completely Adequate","")+0) Adapt the range to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "David Aukerman" wrote: I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
Biff,
Even nicer... thanks! See my above reply for a further question... can this be done in a SUMIF? --David "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(LEFT(A1:A5&0))) I'm assuming the cells don't actually contain the quotes. -- Biff Microsoft Excel MVP "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
No, you can't use SUMIF for this. You can use something like this:
=SUMPRODUCT(--(A1:A5="x"),--LEFT(B1:B5&0)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Even nicer... thanks! See my above reply for a further question... can this be done in a SUMIF? --David "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(LEFT(A1:A5&0))) I'm assuming the cells don't actually contain the quotes. -- Biff Microsoft Excel MVP "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
It's never easy to work directly with mixed data in downstream calculations
Imo, much simpler to strip out the embedded text and have it as pure nums in an adjacent col, eg in B1, copied down: =IF(A1="",0,SUBSTITUTE(SUBSTITUTE(A1,"Completely Inadequate",""),"Completely Adequate","")+0) Then you can easily point to col B for the necessary downstreams using SUMIFs, etc in the usual manner w/o any issue. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "David Aukerman" wrote: Max, Thanks for the quick reply. I like that solution. But now I have a deeper question: can I do this same thing in a SUMIF? E.g., =SUMIF(A1:A100,"Criterion1",B1:B100) has trouble if the B column contains any "1 Completely Inadequate" and "5 Completely Adequate" entries. Any ideas about this? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
I was afraid of that. :) I was hoping to avoid creating an extra column, but
in the end, if it works, it works. Thanks for the input! --David "Max" wrote: It's never easy to work directly with mixed data in downstream calculations Imo, much simpler to strip out the embedded text and have it as pure nums in an adjacent col, eg in B1, copied down: =IF(A1="",0,SUBSTITUTE(SUBSTITUTE(A1,"Completely Inadequate",""),"Completely Adequate","")+0) Then you can easily point to col B for the necessary downstreams using SUMIFs, etc in the usual manner w/o any issue. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "David Aukerman" wrote: Max, Thanks for the quick reply. I like that solution. But now I have a deeper question: can I do this same thing in a SUMIF? E.g., =SUMIF(A1:A100,"Criterion1",B1:B100) has trouble if the B column contains any "1 Completely Inadequate" and "5 Completely Adequate" entries. Any ideas about this? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
Biff,
Thanks, I see how that works. There are some more complicating factors, like occasionally there is an "N/A" entry in the list of values to sum (our B1:B5 column), and the SUMPRODUCT bails when it encounters a non-numeric entry. So I think I might need to create an extra column of strictly numbers, as suggested above. (That is, of course, unless you have an idea about how to ignore the "N/A" values?) --David "T. Valko" wrote: No, you can't use SUMIF for this. You can use something like this: =SUMPRODUCT(--(A1:A5="x"),--LEFT(B1:B5&0)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Even nicer... thanks! See my above reply for a further question... can this be done in a SUMIF? --David "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(LEFT(A1:A5&0))) I'm assuming the cells don't actually contain the quotes. -- Biff Microsoft Excel MVP "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
=SUM(--LEFT(A1:A5)) as an array formula (Control Shift Enter), if the quote
marks aren't included. =SUM(--MID(A1:A5,2,1)), again as an array formula (Control Shift Enter), if the quote marks *are* included. -- David Biddulph "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
OK, try this array formula** :
=SUM(IF(ISNUMBER(--LEFT(A1:A5)),--LEFT(A1:A5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Thanks, I see how that works. There are some more complicating factors, like occasionally there is an "N/A" entry in the list of values to sum (our B1:B5 column), and the SUMPRODUCT bails when it encounters a non-numeric entry. So I think I might need to create an extra column of strictly numbers, as suggested above. (That is, of course, unless you have an idea about how to ignore the "N/A" values?) --David "T. Valko" wrote: No, you can't use SUMIF for this. You can use something like this: =SUMPRODUCT(--(A1:A5="x"),--LEFT(B1:B5&0)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Even nicer... thanks! See my above reply for a further question... can this be done in a SUMIF? --David "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(LEFT(A1:A5&0))) I'm assuming the cells don't actually contain the quotes. -- Biff Microsoft Excel MVP "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
Ooops!
I forgot to include the condition that column A = "something" Note that if "something" is a *TEXT* value then you need to enclose it in double quotes. If "something" is a *NUMERIC* value then you don't need to enclose it in quotes. For example: (A1:A5="Joe") (A1:A5=10) Still array entered** : =SUM(IF((A1:A5="x")*(ISNUMBER(--LEFT(B1:B5))),--LEFT(B1:B5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... OK, try this array formula** : =SUM(IF(ISNUMBER(--LEFT(A1:A5)),--LEFT(A1:A5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Thanks, I see how that works. There are some more complicating factors, like occasionally there is an "N/A" entry in the list of values to sum (our B1:B5 column), and the SUMPRODUCT bails when it encounters a non-numeric entry. So I think I might need to create an extra column of strictly numbers, as suggested above. (That is, of course, unless you have an idea about how to ignore the "N/A" values?) --David "T. Valko" wrote: No, you can't use SUMIF for this. You can use something like this: =SUMPRODUCT(--(A1:A5="x"),--LEFT(B1:B5&0)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Even nicer... thanks! See my above reply for a further question... can this be done in a SUMIF? --David "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(LEFT(A1:A5&0))) I'm assuming the cells don't actually contain the quotes. -- Biff Microsoft Excel MVP "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
I noticed that in your previous response, and after some fiddling, I came up
with =SUMPRODUCT(--(B1:B5="x"),IF(ISNUMBER(--LEFT(A1:A5)),--LEFT(A1:A5))) It looks like this should be functionally equivalent to your suggestion here. In either case, it's working like a charm now... thanks so much! --David "T. Valko" wrote: Ooops! I forgot to include the condition that column A = "something" Note that if "something" is a *TEXT* value then you need to enclose it in double quotes. If "something" is a *NUMERIC* value then you don't need to enclose it in quotes. For example: (A1:A5="Joe") (A1:A5=10) Still array entered** : =SUM(IF((A1:A5="x")*(ISNUMBER(--LEFT(B1:B5))),--LEFT(B1:B5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... OK, try this array formula** : =SUM(IF(ISNUMBER(--LEFT(A1:A5)),--LEFT(A1:A5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Thanks, I see how that works. There are some more complicating factors, like occasionally there is an "N/A" entry in the list of values to sum (our B1:B5 column), and the SUMPRODUCT bails when it encounters a non-numeric entry. So I think I might need to create an extra column of strictly numbers, as suggested above. (That is, of course, unless you have an idea about how to ignore the "N/A" values?) --David "T. Valko" wrote: No, you can't use SUMIF for this. You can use something like this: =SUMPRODUCT(--(A1:A5="x"),--LEFT(B1:B5&0)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Even nicer... thanks! See my above reply for a further question... can this be done in a SUMIF? --David "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(LEFT(A1:A5&0))) I'm assuming the cells don't actually contain the quotes. -- Biff Microsoft Excel MVP "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003, SUM left-most characters
Yeah, that'll work.
Thanks for the feedback! -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... I noticed that in your previous response, and after some fiddling, I came up with =SUMPRODUCT(--(B1:B5="x"),IF(ISNUMBER(--LEFT(A1:A5)),--LEFT(A1:A5))) It looks like this should be functionally equivalent to your suggestion here. In either case, it's working like a charm now... thanks so much! --David "T. Valko" wrote: Ooops! I forgot to include the condition that column A = "something" Note that if "something" is a *TEXT* value then you need to enclose it in double quotes. If "something" is a *NUMERIC* value then you don't need to enclose it in quotes. For example: (A1:A5="Joe") (A1:A5=10) Still array entered** : =SUM(IF((A1:A5="x")*(ISNUMBER(--LEFT(B1:B5))),--LEFT(B1:B5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... OK, try this array formula** : =SUM(IF(ISNUMBER(--LEFT(A1:A5)),--LEFT(A1:A5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Thanks, I see how that works. There are some more complicating factors, like occasionally there is an "N/A" entry in the list of values to sum (our B1:B5 column), and the SUMPRODUCT bails when it encounters a non-numeric entry. So I think I might need to create an extra column of strictly numbers, as suggested above. (That is, of course, unless you have an idea about how to ignore the "N/A" values?) --David "T. Valko" wrote: No, you can't use SUMIF for this. You can use something like this: =SUMPRODUCT(--(A1:A5="x"),--LEFT(B1:B5&0)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Biff, Even nicer... thanks! See my above reply for a further question... can this be done in a SUMIF? --David "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(LEFT(A1:A5&0))) I'm assuming the cells don't actually contain the quotes. -- Biff Microsoft Excel MVP "David Aukerman" <David wrote in message ... I have a range of cells with these (and only these) possible values: "1 Completely Inadequate" "2" "3" "4" "5 Completely Adequate" Is there a way for me to SUM the values of these cells, including the 1s and 5s which include extra text? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Get characters on left of specified character in Excel | Excel Discussion (Misc queries) | |||
| Easiest way to extract characters in a cell LEFT or RIGHT of a sym | Excel Worksheet Functions | |||
| Excel 2003 - Mouse Scrolls Left to Right.. | Excel Discussion (Misc queries) | |||
| HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
| Excel 2003 - Footer Left/Right Margins | Excel Discussion (Misc queries) | |||
Linear Mode
