Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't format cell - have tried unlocking and unprotecting | Excel Discussion (Misc queries) | |||
Grand total same cell in all worksheets | Excel Worksheet Functions | |||
Cell addressing using the content of another cell. | Excel Worksheet Functions | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
Total the sum in a cell | Excel Discussion (Misc queries) |