How do I add row total when cell contains both number and letter .
 

I have a timesheet spreadsheet. Cell contents include hours and coding.
Example: 8 (8 hours regular pay-no letter) or 8V (8 hours vacation pay). I
want to have a total at the end but my formula will only read the cells that
don't have a letter after the number.

If the text value is always "V" you could use something like
=SUM(IF(RIGHT(A18:A21,1)="V",VALUE(LEFT(A18:A21,LE N(A18:A21)-1)),A18:A21))
entered as an array formula (control-shift -return)
If there are other codes than just V, it gets a little more complex. if the
coding is never more than just one character you could use
=SUM(IF(ISNUMBER(A18:A21),A18:A21,VALUE(LEFT(A18:A 21,LEN(A18:A21)-1))))
entered as array formula.

"cbarcroft" wrote:

I have a timesheet spreadsheet. Cell contents include hours and coding.
Example: 8 (8 hours regular pay-no letter) or 8V (8 hours vacation pay). I
want to have a total at the end but my formula will only read the cells that
don't have a letter after the number.

There are several codes used, but only one per cell. Example...
04/15 04/16 04/17
8 8V 8H

Does that make sense? I'd attach the spreadsheet if I could.

"bj" wrote:

If the text value is always "V" you could use something like
=SUM(IF(RIGHT(A18:A21,1)="V",VALUE(LEFT(A18:A21,LE N(A18:A21)-1)),A18:A21))
entered as an array formula (control-shift -return)
If there are other codes than just V, it gets a little more complex. if the
coding is never more than just one character you could use
=SUM(IF(ISNUMBER(A18:A21),A18:A21,VALUE(LEFT(A18:A 21,LEN(A18:A21)-1))))
entered as array formula.

"cbarcroft" wrote:

I have a timesheet spreadsheet. Cell contents include hours and coding.
Example: 8 (8 hours regular pay-no letter) or 8V (8 hours vacation pay). I
want to have a total at the end but my formula will only read the cells that
don't have a letter after the number.

The second equation should work then

"carriebarcroft" wrote:

There are several codes used, but only one per cell. Example...
04/15 04/16 04/17
8 8V 8H

Does that make sense? I'd attach the spreadsheet if I could.

"bj" wrote:

If the text value is always "V" you could use something like
=SUM(IF(RIGHT(A18:A21,1)="V",VALUE(LEFT(A18:A21,LE N(A18:A21)-1)),A18:A21))
entered as an array formula (control-shift -return)
If there are other codes than just V, it gets a little more complex. if the
coding is never more than just one character you could use
=SUM(IF(ISNUMBER(A18:A21),A18:A21,VALUE(LEFT(A18:A 21,LEN(A18:A21)-1))))
entered as array formula.

"cbarcroft" wrote:

I have a timesheet spreadsheet. Cell contents include hours and coding.
Example: 8 (8 hours regular pay-no letter) or 8V (8 hours vacation pay). I
want to have a total at the end but my formula will only read the cells that
don't have a letter after the number.

=SUM(A1:A4)+(COUNTIF(A1:A4,"8v")*8)+(COUNTIF(A1:A4 ,"8H")*8)...etc etc for
additional codes........

Vaya con Dios,
Chuck, CABGx3


"cbarcroft" wrote in message
...
I have a timesheet spreadsheet. Cell contents include hours and coding.
Example: 8 (8 hours regular pay-no letter) or 8V (8 hours vacation pay).
I
want to have a total at the end but my formula will only read the cells
that
don't have a letter after the number.