Formula for adding values that have a letter at the end in Excel 2
Give this array-entered** formula a try...
=24*TEXT(SUM(IF((RIGHT(A1:A200)="S"),--LEFT(A1:A200,LEN(A1:A200)-1),"")),"[h]:mm")
**Commit formula using Ctrl+Shift+Enter, not just Enter by itself.
Adjust the range to suit your needs; replace the "S" with "A" and "P" for
your other conditions.
--
Rick (MVP - Excel)
"LMarks" wrote in message
...
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.
|