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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,LEN(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:A21,LEN(A18:A21)-1))))
entered as array formula.
 
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.
 
The second equation should work then

carriebarcroft said:
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.
Click to expand...
 
=SUM(A1:A4)+(COUNTIF(A1:A4,"8v")*8)+(COUNTIF(A1:A4,"8H")*8)...etc etc for
additional codes........

Vaya con Dios,
Chuck, CABGx3
 
Back
Top