Forum Discussion

Lucy_McMahon's avatar
Lucy_McMahon
Copper Contributor
Sep 09, 2022
Solved

Trying to calculate sum with letter in the front

Green cells are filled with 8 automatically. Entries into other cells subtract 8 automatically instead of what is entered, since there is text as well. I'm trying to get it to subtract the numb...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Sep 09, 2022

    Lucy_McMahon The attached is one option.  I used 10*countif("O") to adjust from 80 to 60 hours but that is obviously very case specific to this case.  Then I use a subtraction.  Note I also formatted it all as a table so all the formulas autofill all the rows (instead of an array formula) but could be reformatted not using a table.  I also added conditional formatting to automatically color those columns based on the letter entered (or blank) but only had a subset at the time.

Lucy_McMahon's avatar
Lucy_McMahon
Copper Contributor
Sep 09, 2022

mathetes 

 

 

If it will be helpful, I can put each person on one row with 10 columns, instead of how it is currently. It would take while as I have 28 sheets with 35 or so people that are already filled in with the data to avoid the merged cells. Then I can use array formulas. The letters are already coded but are only consequential in the YTD calculations:

 

 

 

 

 

mtarler's avatar
mtarler
Silver Contributor
Sep 09, 2022
maybe you already know this and use it but certain formulas like sum can be used as a 3-d formula meaning you can sum across multiple sheets as a range. SO if you do these sums for each employee on the same location on each of the bi-weekly sheets then the YTD formula is simply ='1st Sheet':'Last Sheet'!M2 if that value is located at M2 on each of those sheets