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!

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!

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!

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!

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!

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!

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?

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?

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!

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!

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!

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!

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!

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!