COUNTIF(s) for date range on referenced sheet
Hello,
I've been searching all forums and can't seem to find a formula to fit what I need. I feel like it should be relatively simple but nothing I've tried has worked.
I'm trying to get a count of patients who had their consultation during the year of 2020. I am referencing another sheet for the count.
The columns are:
I use the "Info Added to NexTech" as a way to count the total in a separate count, so I thought it could be used, but maybe it's unnecessary? I've tried with and without and it still won't calculate.
The first (include the checkbox count):
=COUNTIFS({Reference1}, 1, AND({Reference2}, (@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 12, 31))
Reference 1 = "Info Added to NexTech" column
Reference 2 = "Initial Consult Date" column
The second (without the checkbox count):
=COUNTIF({Reference 2}, (@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 12, 31))
There are no blank date fields.
Thank you!
Best Answer
-
Try this...
=COUNTIFS({Reference1}, 1, {Reference2}, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 12, 31))
Make sure you're references are only the one column selected and multiple columns too. Did that work?
Answers
-
I realized I was missing a couple of the end ) so I tried both again, but still having the same error.
-
Try this...
=COUNTIFS({Reference1}, 1, {Reference2}, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 12, 31))
Make sure you're references are only the one column selected and multiple columns too. Did that work?
-
It did work! Thank you so much.
-
Awesome. Glad I could you out!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!