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!