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

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!

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

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!

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

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!

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

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!

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

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!

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

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!

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

lol @ even for me :-)

Yep that worked 100%

again thanks for all the help!

"Bob Phillips" wrote:

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!

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

Glad we resolved that <go

--
---
HTH

Bob


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



"KidMalicious" wrote in message
...
lol @ even for me :-)

Yep that worked 100%

again thanks for all the help!

"Bob Phillips" wrote:

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!