Help counting items in a column in another sheet if between defined date ranges
![TamieC](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
Best Answer
-
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))
Answers
-
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))
-
That did it! Thanks for the quick response. I was certainly trying to over think it by combining a couple of formulas for dates.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!