CountIF between dates against specific criteria

[J_Kelly]

I am trying to use a count if to count the number of times a date appears within a range of columns while that same count also follows an additional criteria.

The below formula to me should work, but i have had to break it down to column by column to get a value.

=COUNTIFS('Sheet1'!AK:BM,">="&D14,'Sheet1'!AK:BM,"<="&D15,'Sheet1'!BU:BU,"Location")

Where D14 and D15 are a date range that will change weekly.

If I remove the Location option the count works however as soon as I reintroduce it the formula fails.

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
With countifs all the ranges must be the same size, try it like

=SUMPRODUCT((Sheet1!AK2:BM1000>=D14)*(Sheet1!AK2:BM1000<=D15)*(Sheet1!BU2:BU1000="Location"))

 

[J_Kelly]

Hi & welcome to MrExcel.
With countifs all the ranges must be the same size, try it like

=SUMPRODUCT((Sheet1!AK2:BM1000>=D14)*(Sheet1!AK2:BM1000<=D15)*(Sheet1!BU2:BU1000="Location"))

Thank you, its worked perfectly! I saw the SUMPRODUCT function around but didn't understand how it worked.

 

[Fluff]

You're welcome & thanks for the feedback.