Formulas

[HunterJan10]

Good morning, Everyone,

I am currently trying to figure out formula or function for my employee vacation tracker spreadsheet. I am not sure if it is possible, but what I would like to happen is when an employee uses a vacation day, I input "V" on the calendar and I would like for that input to take away 8 hours from their total vacation hours on another sheet. If I need to provide more explanation, please let me know. I appreciate any advice given.

[#2]

@HunterJan10 

=I2-SUMPRODUCT(($B$1:$E$1=H2)*($B$2:$E$28="V")*8)

This formula could be a solution. The results can be displayed on another worksheet as well.

  

[#3]

@OliverScheurich How would I make the formula account for each monthly tab below? I have attached photos of my sheet. The employee's tab is where the total hours are located.

 

[#4]

@HunterJan10 

=B2-SUMPRODUCT(($A2=january!$A$2:$A$23)*(january!$B$2:$AF$23="V"))*8-SUMPRODUCT(($A2=february!$A$2:$A$23)*(february!$B$2:$AC$23="V"))*8

You can try a formula like this. For months march to december you can add a SUMPRODUCT expression to the formula accordingly. However there should be a solution with a shorter formula.

 

[#5]

I appreciate your help on this. What if I wanted to include "P" and "S" as full 8 hours as well. And the HD to equal 4 hours?

[#6]

@HunterJan10 

=B2-SUM(IF(A2=january!$A$2:$A$23,january!$AG$2:$AG$23))*8-SUM(IF(A2=february!$A$2:$A$23,february!$AD$2:$AD$23))*8

I'd suggest a SUM(IF formula which refers to the "Total days" column of each sheet. You have to enter this formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The missing SUM(IF expressions for march to december can be added accordingly.

=SUMPRODUCT(COUNTIF(B2:AF2,{"V","S","P"}))+COUNTIF(B2:AF2,"HD")*0.5

This is the formula in the "Total days" column in the january sheet. The result is used in the SUM(IF formula in the "employee names" sheet.