Re: Help with summing numbers in cells that also contain text

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" <fbl3@xxxxxxxx> wrote in message news:O2Bns$A7HHA.5160@xxxxxxxxxxxxxxxxxxxxxxx
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" <mtmw@xxxxxxxxxxxxxxx> wrote in message news:ug4ieR55HHA.5740@xxxxxxxxxxxxxxxxxxxxxxx
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" <mslater518@xxxxxxxxxxx> wrote in message news:4oudnQvnH9LW303bnZ2dnUVZ_rmjnZ2d@xxxxxxxxxxxxxx
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


"macropod" <invalid@xxxxxxxxxxxxxxx> wrote in message news:%23Lku3ix5HHA.2380@xxxxxxxxxxxxxxxxxxxxxxx
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" <mslater518@xxxxxxxxxxx> wrote in message news:6K2dnQUAi4OfvE3bnZ2dnUVZ_g-dnZ2d@xxxxxxxxxxxxxx
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





.

Relevant Pages