Summing first 2 characters of a cell (some non-numeric)

[KidMalicious]

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!

 

[Bob Phillips]

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]

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]

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]

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]

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]

lol @ even for me

Yep that worked 100%

again thanks for all the help!

 

[Bob Phillips]

Glad we resolved that <go>

--
---
HTH

Bob


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