COUNTIFS

New Contributor

=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

2 Replies

@JeffSauers 

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"))

@Hans Vogelaar 

 

The first solution worked nicely, Thank you for the assistance.