COUNTIFS

%3CLINGO-SUB%20id%3D%22lingo-sub-2144199%22%20slang%3D%22en-US%22%3ECOUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2144199%22%20slang%3D%22en-US%22%3E%3CP%3E%3DCOUNTIFS(AU3%3AAU100%2C%22%26lt%3B%22%20%26amp%3B%20(M3-12)%2CAV3%3AAW100%2C%22%3DSIKORA%22)%3C%2FP%3E%3CP%3EWhere%20Column%20AU%20are%20Dates%20(M3%20is%20the%20date%20that%20payroll%20ends%2C%20ex.%202%2F19%2F2021%2C%20so%20M3-12%20%3D%202%2F7%2F2021)%2C%20AV3%3AAW100%20are%20the%20columns%20with%20contain%20employee%20names.%20I%20want%20to%20count%20the%20number%20of%20time%20Sikora%20worked%20in%20week%201%20of%20the%20payroll%20(01%2F31%2F2021-02%2F06%2F2021).%20When%20I%20break%20up%20the%20COUNTIFS%20separately%20%3DCOUNTIFS(AU3%3AAU100%2C%22%26lt%3B%22%20%26amp%3B%20(M3-12))%20It%20returns%2042%20which%20is%20the%20correct%20number%20of%20times%20I%20have%20entries%20for%20work%20done%20in%20week%201%2C%20COUNTIFS(AV3%3AAW100%2C%22%3DSIKORA%22)%20returns%2023%20which%20is%20correct%20for%20the%20entire%20payroll.%26nbsp%3B%20When%20I%20combine%20them%20to%20the%20original%20COUNTIFS%20I%20get%20%23VALUE!.%20It%20should%20get%20a%20result%20of%2016%20for%20the%2016%20calls%20SIKORA%20did%20in%20WEEK%201%20of%20th%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2144199%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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.