Trying to do a CountIFS and add a date range
I am working on a Summary Sheet and I am trying to add three options together to get a count. I have the first two but not sure how to add a date range. An example is below:
For an Assigned Activity, Status for Q1. So far, I have the Activity Name, the Registration Status but I can’t figure out how to get a date range (since my sheet is year to date) from one column
The Attempt End Date is the column name, but I need a range from 1/1/20 – 3/31/20 and then again from 4/1/20 – 6/31/20. Am doing wrong (like need a separate column)? Thank you
=COUNTIFS([Activity Name]:[Activity Name], "Kronos Employee Series - Open Timecard", [Registration Status]:[Registration Status], "Completed")
Answers
-
Try something like this...
=COUNTIFS([Activity Name]:[Activity Name], "Kronos Employee Series - Open Timecard", [Registration Status]:[Registration Status], "Completed", [Attempt End Date]:[Attempt End Date], AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul That worked. Thank you so much. I really appreciate it.
-
I do have another scenario to add to this. I have blanks in the Attempt End Date. Is there a way to add one more thing that says if the cell is blank but meets other criteria to include in the count? Thank you
-
So you want to COUNTIFS
[Activity Name] = "Kronos Employee Series - Open Timecard"
[Registration Status] = "Completed"
[Attempt End Date] = Date Range OR Blank
??
If that is correct, give this a go...
=COUNTIFS([Activity Name]:[Activity Name], "Kronos Employee Series - Open Timecard", [Registration Status]:[Registration Status], "Completed", [Attempt End Date]:[Attempt End Date], OR(@cell = "", AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
You are my hero. Thank you again.
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!