I'll bet that is because the newsreader wrapped it. This one should work,
even for you

=SUM(IF(ISNUMBER(--LEFT(AA19:AA22,
FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,
FIND(" ",AA19:AA22)-1))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"KidMalicious" wrote in message
...
I know, but it didn't work when I put it in, so I changed it

anyway thanks for your help I appreciated it!

"Bob Phillips" wrote:

Mine was more generic, it was picking up any number before the first
space.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"KidMalicious" wrote in message
...
That didn't work bob it just evaluates to zero.

but I followed your thinking and just replaced you Find with the number
2
like this

=SUM(IF(ISNUMBER(--LEFT(AA19:AA22,2)),--(LEFT(AA19:AA22,2))))

and it worked perfectly!

thanks a million for all the help

"Bob Phillips" wrote:

Try this array formula

=SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND("
",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1))))

as an array formula commit with Ctrl-Shift-Enter

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"KidMalicious" wrote in
message
...
Hi there,

I am trying to sum the first 2 characters of a cell which works fine
when
they are all numeric but when one isn't it falls over

The data:
12 hours 0 mins
Pending
4 hours 0 mins
8 hours 0 mins

Formula used:
=SUMPRODUCT(--LEFT(AA19:AA22,2))

Which as I said works fine if there's a value instead of pending.

I don't really want to use a holding cell for the values as there is
a
chance it could get overwritten by the user (they cut and paste into
the
spread sheet)

Is there a way to filter the result of the left to ignore "Pe" (this
is
the
only possible non numeric value)

Thank you!