Feb 17 2021 12:21 PM
=COUNTIFS(AU3:AU100,"<" & (M3-12),AV3:AW100,"=SIKORA")
Where Column AU are Dates (M3 is the date that payroll ends, ex. 2/19/2021, so M3-12 = 2/7/2021), AV3:AW100 are the columns with contain employee names. I want to count the number of time Sikora worked in week 1 of the payroll (01/31/2021-02/06/2021). When I break up the COUNTIFS separately =COUNTIFS(AU3:AU100,"<" & (M3-12)) It returns 42 which is the correct number of times I have entries for work done in week 1, COUNTIFS(AV3:AW100,"=SIKORA") returns 23 which is correct for the entire payroll. When I combine them to the original COUNTIFS I get #VALUE!. It should get a result of 16 for the 16 calls SIKORA did in WEEK 1 of th
Feb 17 2021 12:36 PM
All ranges in COUNTIFS must have the same size. You can split it up into two COUNTIFS:
=COUNTIFS(AU3:AU100,"<"&M3-12,AV3:AV100,"SIKORA")+COUNTIFS(AU3:AU100,"<"&M3-12,AW3:AW100,"SIKORA")
Or use SUMPRODUCT:
=SUMPRODUCT((AU3:AU100<M3-12)*(AV3:AW100="SIKORA"))
Feb 17 2021 01:48 PM