Forum Discussion
Sum help
Okay, I am trying to make a sheet to track PTO hours and add/sort them by the number of hours based on what kind of hours they are. Example, in one month, Suzy may have 3S, 5V, 7S, 5V, 5V. I am seeking a formula to add the hours (3+7) because there is an S beside the number without having to make a whole new column beside every date for the category to input a sumif. Is there a formula to add the 3S+7S to get an output of 10S (10 sick hours) and then a 5V+5V+5V to get an output of 15 vacation hours?
6 Replies
Possible, but not very good practice. Please see attached, though slightly different from the solution you found yourself.
Agreed. A better option is to add the text through custom cell formatting. Standard SUM/SUMIF/SUMIFS, etc can then be used.
- I suggest you remove all the text from the numeric values.
Assuming you have 3 in cell A1 and 7 in cell A2
Then, you can do the following
=SUM(A1:A2)&"S"
That will return 10SThanks for the response Abiola1 . Turns out I did some more digging and found that there is indeed a way with this formula: =SUMPRODUCT(--(LEFT(B5:AF5,1)="S"),IF(ISERROR(--RIGHT(B5:AF5,LEN(B5:AF5)-1)),0,--RIGHT(B5:AF5,LEN(B5:AF5)-1)))
The letter must come before the number, and to use the formula you must hit ctrl+alt+enter. It works too!
