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
Yes, we have 3 employees who don't work all 10 days. They only work 8 days @ 7.5 hours a day for 60 hours in the bi-weekly pay period.
mtarler's avatar
mtarler
Silver Contributor
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 13, 2022

    mtarler 

     

    Thank you so much! This worked perfectly. 

Resources