Excel 2003, SUM left-most characters

  • Thread starter Thread starter David Aukerman
  • Start date Start date
D

David Aukerman

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!
 
Maybe this:

=SUMPRODUCT(--(LEFT(A1:A5&0)))

I'm assuming the cells don't actually contain the quotes.
 
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
 
Biff,

Even nicer... thanks! See my above reply for a further question... can this
be done in a SUMIF?

--David
 
No, you can't use SUMIF for this. You can use something like this:

=SUMPRODUCT(--(A1:A5="x"),--LEFT(B1:B5&0))
 
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
 
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
 
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
 
=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.
 
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.
 
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.
 
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
 
Back
Top