Help with summing numbers in cells that also contain text

  • Thread starter Thread starter Michael Slater
  • Start date Start date
M

Michael Slater

Hello all,

I have a work schedule where the column heading is the employee's name and
the cells below indicate what the employee's status was for that date, as
follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column "C"
using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The only
way I know of to SUM column "C" is to copy and paste the values, then
convert to a number.

Does anyone know an easier way to do this without the copy,paste,convert, if
possible?

Thanks,

Mike
 
Hi Michael,

The following array formula will sum your values in Column B without the need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value (eg 8hrs), change the '0' in the formula to that value. Note
though that this will assume 8hrs for any blank cells in the range also.

Cheers
 
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


macropod said:
Hi Michael,

The following array formula will sum your values in Column B without the
need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value (eg
8hrs), change the '0' in the formula to that value. Note though that this
will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Michael Slater said:
Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the copy,paste,convert,
if possible?

Thanks,

Mike
Click to expand...
Click to expand...
 
Hi Michael,

Just as an addition you can also get your original formula to do
the same conversion as your copy and paste step.

Original formula
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

Becomes
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1
or
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0
or even
=--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

All three will convert your text answer to numbers.

HTH
Martin



Michael Slater said:
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


macropod said:
Hi Michael,

The following array formula will sum your values in Column B without the
need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value
(eg 8hrs), change the '0' in the formula to that value. Note though that
this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Michael Slater said:
Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the
copy,paste,convert, if possible?

Thanks,

Mike
Click to expand...
Click to expand...
Click to expand...
 
Hi guys,

I am trying to do a SUM using this formula on a same type of column:
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml


But the results is 0. Do have to plug anything else in the formula? I
played with it and cannot figure it out.

Thanks in advance,

Frank

MartinW said:
Hi Michael,

Just as an addition you can also get your original formula to do
the same conversion as your copy and paste step.

Original formula
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

Becomes
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1
or
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0
or even
=--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

All three will convert your text answer to numbers.

HTH
Martin



Michael Slater said:
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


macropod said:
Hi Michael,

The following array formula will sum your values in Column B without the
need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value
(eg 8hrs), change the '0' in the formula to that value. Note though that
this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the
copy,paste,convert, if possible?

Thanks,

Mike
Click to expand...
Click to expand...
Click to expand...
 
You can use TEXT or format the cell with 1, 2, 3, ... decimals
Using TEXT, try something like this:

=TEXT( SUM( A1:A12 ), "#.####")


--

Rodrigo Ferreira
Regards from Brazil


Frank Beltre said:
Hi guys,

I am trying to do a SUM using this formula on a same type of column:
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml


But the results is 0. Do have to plug anything else in the formula? I
played with it and cannot figure it out.

Thanks in advance,

Frank

MartinW said:
Hi Michael,

Just as an addition you can also get your original formula to do
the same conversion as your copy and paste step.

Original formula
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

Becomes
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1
or
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0
or even
=--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

All three will convert your text answer to numbers.

HTH
Martin



Michael Slater said:
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


Hi Michael,

The following array formula will sum your values in Column B without
the need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value
(eg 8hrs), change the '0' in the formula to that value. Note though
that this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the
copy,paste,convert, if possible?

Thanks,

Mike
Click to expand...
Click to expand...
Click to expand...
 
Hi Frank,

Apart from changing the data range to suit your data, the formula will works as posted.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Frank Beltre said:
Hi guys,

I am trying to do a SUM using this formula on a same type of column:
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml


But the results is 0. Do have to plug anything else in the formula? I
played with it and cannot figure it out.

Thanks in advance,

Frank

MartinW said:
Hi Michael,

Just as an addition you can also get your original formula to do
the same conversion as your copy and paste step.

Original formula
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

Becomes
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1
or
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0
or even
=--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

All three will convert your text answer to numbers.

HTH
Martin



Michael Slater said:
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


Hi Michael,

The following array formula will sum your values in Column B without the
need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value
(eg 8hrs), change the '0' in the formula to that value. Note though that
this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the
copy,paste,convert, if possible?

Thanks,

Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top