Help with summing numbers in cells that also contain text

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
 
M

macropod

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
 
M

Michael Slater

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...
 
M

MartinW

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...
 
F

Frank Beltre

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...
 
R

Rodrigo Ferreira

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...
 
M

macropod

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a dynamic chart range where the range to be graphed depends on the contents of a cell 0
Sum value between two dates and copy to new cell 7
Time Sum in Excel 1
CALCULATING DATES 3
Determining Change in a cell Value.... 4
Summing values for a range of dates 2
SUMIF Formula Help 8
Summing rows based on other column values 7

Top