Countifs Reference Another Sheet - Help
This is the formula I am trying to use with reference to another sheet. Range 5 I want to only count if ticket status is "resolved" and Range 6 if in the month of Dec 2023. Error "Unparsable".
=COUNTIFS({Field EDI Request Sheet Range 5}, [TICKET STATUS], "RESOLVED", [{Field EDI Request Sheet Range 6}, (LAST STATUS DATE}, >=DATE(2023, 11, 30), <=DATE(2024, 1, 1)])
Please help!
Answers
-
Hi
There are a few problems with the syntax and some random characters.
This is your formula. I have put the problem areas in bold
=COUNTIFS({Field EDI Request Sheet Range 5}, [TICKET STATUS], "RESOLVED", [{Field EDI Request Sheet Range 6}, (LAST STATUS DATE}, >=DATE(2023, 11, 30), <=DATE(2024, 1, 1)])
The syntax for COUNTIFS is range1, criteria 1, range 2, criteria 2, range 3, criteria 3.
Something like this
=COUNTIFS({Field EDI Request Sheet Range 5}, "RESOLVED", {Field EDI Request Sheet Range 6}, >DATE(2023, 11, 30), {Field EDI Request Sheet Range 6},<DATE(2024, 1, 1))
Will look in the range you defined as {Field EDI Request Sheet Range 5} and search for "RESOLVED"
And then in the range you defined as {Field EDI Request Sheet Range 6} and look for a date after 30th November 2023 (note I removed the equals as this would include the 30th, and you just want dates after)
And finally in the range you defined as {Field EDI Request Sheet Range 6} and look for a date before 1st January 2024 (note I removed the equals again)
It will count the rows where all three things are true.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!