Formula for adding values that have a letter at the end in Excel 2
Insert a column next to the column where you have 1:45A etc etc and copy this
formula all the way down
=--MID(A2,1,LEN(A2)-1)
This will make your 1:45A into 1:45
Now for all sums of "A" use this formula and press Ctrl+Shift+Enter:
=SUM(IF(ISNUMBER(FIND("A",A2:A16)),B2:B16))*24
(Assuming your range is from A2:A16)
2nd part of your questions simply add 0.40 to your formula or is there
something I am missing
"LMarks" wrote:
We use 3 letter codes (A, S, P) for leave taken by an employee. The number
of hours is entered as H:MM, then the letter leave code (e.g., 1.45A, 2.30S,
4:15P). I need a formula to add the total number of hours in each row for
the three letter codes. I have spent hours searching for formulas online and
am not sure which functions to use (SUMIF, SUM, etc.)
I also need a formula that would convert the a total number of leave hours
e.g., 147.70=148.10, 1233.89=1234.29, 602.97=603.37. Any help would be
greatly appreciated.
|