Last 7 days with multiple conditions
I have an external sheet named Sheet 1. I want to look at Column 1 to see if it is in the last 7 days and then look at Column 2 to see if it has "Open" in the cell. If it meets both conditions, I want it to count it.
The 1st part of the formula works by itself:
=COUNTIFS({Sheet Range 1}, AND (@cell >= TODAY(-7), @cell <= TODAY()))
I am sure this part of the formula is what is wrong:
AND({Sheet 1 Range 2}, "Open", @cell))
Here is the entire formula:
=COUNTIFS({Sheet Range 1}, AND (@cell >= TODAY(-7), @cell <= TODAY()) AND({Sheet 1 Range 2}, "Open", @cell))
Thanks in advance.
Best Answer
-
Sorry about that. I missed the extra space after the AND function.
=COUNTIFS({[Kevin] Service Support KPI - Q2 Range 1}, AND(@cell >= TODAY(-7), @cell <= TODAY()), {[Kevin] Service Support KPI - Q2 Range 2}, @cell = "Open")
Answers
-
Try this...
=COUNTIFS({Sheet Range 1}, AND (@cell >= TODAY(-7), @cell <= TODAY()), {Sheet 1 Range 2}, @cell = "Open")
-
I tried that and it didn't work, it is showing #UNPARSEABLE.
-
Can you copy/paste directly from the sheet to here the exact formula that is giving the error message?
-
Here is the exact formula:
=COUNTIFS({[Kevin] Service Support KPI - Q2 Range 1}, AND (@cell >= TODAY(-7), @cell <= TODAY()), {[Kevin] Service Support KPI - Q2 Range 2}, @cell = "Open")
Thank you,
-
Sorry about that. I missed the extra space after the AND function.
=COUNTIFS({[Kevin] Service Support KPI - Q2 Range 1}, AND(@cell >= TODAY(-7), @cell <= TODAY()), {[Kevin] Service Support KPI - Q2 Range 2}, @cell = "Open")
-
It worked. Thank you for the quick response!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!