Forum Discussion
Formulas
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 w...
=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"))*8You 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.
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?
=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))*8I'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.5This 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.