Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for adding values that have a letter at the end in Excel 2
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for adding values that have a letter at the end in Excel 2
Hmm... this may work:
=SUMPRODUCT(--(RIGHT(A1:A100,1)="A"),--(LEFT(A1:A100,FIND(":",A1:A100)-1)))+INT(SUMPRODUCT(--(RIGHT(A1:A100,1)="A"),--(MID(A1:A100,FIND(":",A1:A100)+1,2)))/60)&":"&MOD(SUMPRODUCT(--(RIGHT(A1:A100,1)="A"),--MID(A1:A100,FIND(":",A1:A100)+1,2)),60) This will return an error if you have any blank cells or data that does not fit your specified format of H:MM. If you do have blanks in your range, you could fill them with the text value 0:00. HTH Elkar "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding letter at the end of cell | Excel Discussion (Misc queries) | |||
Adding up letter values | Excel Discussion (Misc queries) | |||
Dificulty adding alphanumeric values either one letter or two | New Users to Excel | |||
Adding a number to a letter of the alphabet to get a letter | Excel Worksheet Functions | |||
My first post - Please Help (Formula for adding Values) | Excel Discussion (Misc queries) |