COUNTIFS with date range within a cell

[cgaechter]

Hello,

I am trying to build a template for use every month, where the date range would change for each month to the month in question. The same countifs formula would be used in multiple cells with slight variations but the date range part will always be the same (except for the change each month). Rather than having to update each cell's formula each month I was hoping to point the formula to a cell where I would have the date min and max. Is there a way to do that? So instead of:

=COUNTIFS('Sheet1'!D:D, ">9/30/2023",'Sheet1'!D:D, "<11/1/2023"), I would like to have it to Cell A1 and A2 where A1 = 9/30/2023 and A2 = 11/1/2023 and each month I only need to update those two cells.

I hope this makes sense. There are other criteria I want to apply which is why I'm using countifs but maybe there is another formula.

Thanks!

Camila

[#2 · Accepted solution]

@cgaechter 

=COUNTIFS(Sheet1!D:D, ">"&A1,Sheet1!D:D, "<"&A2)

=SUMPRODUCT((Sheet1!D:D>A1)*(Sheet1!D:D<A2))

These formula return the intended result in my sheet.

[#3]

amazing !! thank you the first one worked with the &
Thanks :)