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!