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!