Help counting items in a column in another sheet if between defined date ranges

TamieC

Collecting the count of an item column in another sheet if the date range is between two dates.

I am using this formula and it works well, but a bit labor when I have 48 that have different source and date ranges. Plus thinking of 2025, it will be a pain to redo them when the next year rolls around vs just changing the date range cells.

=COUNTIF({Source Table Source Column1}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 31)))

=COUNTIF({Source Table Source Column1}, AND(@cell >= DATE(2024, 2, 1), @cell <= DATE(2024, 2, 29)))

...

=COUNTIF({Source Table Source Column2}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 31)))

=COUNTIF({Source Table Source Column2}, AND(@cell >= DATE(2024, 2, 1), @cell <= DATE(2024, 2, 29)))

Etc.

To overcome this issue so I only need to update the start date (2024-01-01) and end date (2024-01-31) etc. I added a columns to this working sheet (date format) where the formulas in the VALUE column.

I thought this work, but invaliddatatype

=COUNTIF({NP SUS 2024 Sheet NPSUS Date}, AND(@cell >= DATE([Range Start]2, @cell <= DATE([Range End]2))))

and this produces unparseable

=COUNTIFS({NP SUS 2024 Sheet NPSUS Paid Date}, and >= DATE([Range Start]2, <= DATE([Range End]2)

Thanks in advance for help with this solution.

--TamieC

Paul Newcome

That first one with the AND is the closest with just a minor syntax tweak. Lets start with that and see if it fixes the issue. You do not need the DATE function when referencing a cell. Just make sure the column is a date type column housing date type data.

=COUNTIF({NP SUS 2024 Sheet NPSUS Date}, AND(@cell >= [Range Start]2, @cell <= [Range End]2))

Paul Newcome

That first one with the AND is the closest with just a minor syntax tweak. Lets start with that and see if it fixes the issue. You do not need the DATE function when referencing a cell. Just make sure the column is a date type column housing date type data.

=COUNTIF({NP SUS 2024 Sheet NPSUS Date}, AND(@cell >= [Range Start]2, @cell <= [Range End]2))

TamieC

That did it! Thanks for the quick response. I was certainly trying to over think it by combining a couple of formulas for dates.

Paul Newcome

Happy to help. 👍️