Countif formula with date range
Hi,
I'm looking for a formula which will give me the total number rows for a certain date range from my master list for my metrics.
For example, I want to count the number of rows which have dates between 01/01/2024 and 01/31/24, so this will tell me how many county request have been captured within that date range.
Can someone please help?
Formula: =COUNTIFS({Ombud Complaint and Constituent Issue Trac Range 6}, [Primary Column]@row, >=DATE(2023, 12, 31), {Ombud Complaint and Constituent Issue Trac Range 3}, <=DATE(2024, 1, 31))
Answers
-
Hi @Canteaug,
Your COUNTIFS is missing a column (for the first date criteria), but you can also simply this a bit further with an AND. Your formula would be something like this:
=COUNTIFS({County reference},[Primary column]@row,{Date column},AND(@cell > DATE(2023, 12,31), @cell <= DATE(2024, 1, 31)))
The {x} references here have been here to say which column they should correlate too, you may have them already set as the default numbered ones so when setting up your formula may look a little bit different if they're already created.
Hopefully this helps though, but if you have any problems/questions then just post! 🙂
-
You have two different ranges for the count ranges of the dates. Is that intentional? Are you looking to see if a start date is after 12/31 and the end date before 1/31? That error message generates when the size of two different ranges don't match in a function like this where they need to.
Another note, I would remove the "=" after the ">" in your first criterion if you don't want to capture 12/31.
I hope this helps!
Best,
Zach Hall
Training Delivery Manager / Charter Communications
-
@Zachary Hall So I'm trying to count the number of each county for every month. I am pulling the dates received and the county information from my master sheet.
-
@Nick Korna I tried the formula you suggested and added the date column that is pull from the master and still no luck :(
-
Okay. Try this:
- =COUNTIFS({Ombud Complaint and Constituent Issue Trac Range 6}, [Primary Column]@row, {Ombud Complaint and Constituent Issue Trac Range 3}, >DATE(2023, 12, 31), {Ombud Complaint and Constituent Issue Trac Range 3}, <=DATE(2024, 1, 31))
Best,
Zach Hall
Training Delivery Manager / Charter Communications
-
@Zachary Hall You saved my life! Thank you so much. :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!