Need help with COUNTIF

When I use this formula, it works fine,
=COUNTIFS([Date Request Submitted -System]:[Date Request Submitted -System], <=TODAY(), [Date Request Submitted -System]:[Date Request Submitted -System], >TODAY(-7)) + COUNTIFS(Status:Status, OR(@cell = "Submitted", @cell = "Acknowledged", @cell = "Revisit Underway"))
As soon as I add a 3rd range,criteria it states #NESTED CRITERIA.
=COUNTIFS([Date Request Submitted -System]:[Date Request Submitted -System], <=TODAY(), [Date Request Submitted -System]:[Date Request Submitted -System], >TODAY(-7)) + COUNTIFS(Status:Status, OR(@cell = "Submitted", @cell = "Acknowledged", @cell = "Revisit Underway") + COUNTIFS([Assigned To]:[Assigned To], OR(@cell = "Glenn Frazer", @cell = "Alan Jochim", @cell = "Anthony Double")))
Best Answer
-
You have a misplaced closing parenthesis. Take one away from the very end and use it to close out the second COUNTIFS before adding the third COUNTIFS.
Although... Exactly what are you wanting to count with this? Your current formula will count up all rows based on each COUNTIFS individually regardless of the data in the others. Basically, your first COUNTIFS is going to count all rows that are greater than TODAY(-7) and disregard the Status and Assigned to. Then your second is going to count all rows that have one of those statuses regardless of the date and Assigned to. Then your third COUTNIFS will count all rows that have one of those people assigned regardless of date and status. Then you are going to add all of those counts together.
If you are looking to count only rows that meet all of those range/criteria sets, you would need to put all range/criteria sets into a single COUNTIFS.
Answers
-
You have a misplaced closing parenthesis. Take one away from the very end and use it to close out the second COUNTIFS before adding the third COUNTIFS.
Although... Exactly what are you wanting to count with this? Your current formula will count up all rows based on each COUNTIFS individually regardless of the data in the others. Basically, your first COUNTIFS is going to count all rows that are greater than TODAY(-7) and disregard the Status and Assigned to. Then your second is going to count all rows that have one of those statuses regardless of the date and Assigned to. Then your third COUTNIFS will count all rows that have one of those people assigned regardless of date and status. Then you are going to add all of those counts together.
If you are looking to count only rows that meet all of those range/criteria sets, you would need to put all range/criteria sets into a single COUNTIFS.
-
Hi @tgattsh , You have a parentheses out of place. Try:
=COUNTIFS([Date Request Submitted -System]:[Date Request Submitted -System], <=TODAY(), [Date Request Submitted -System]:[Date Request Submitted -System], >TODAY(-7)) + COUNTIFS(Status:Status, OR(@cell = "Submitted", @cell = "Acknowledged", @cell = "Revisit Underway")) + COUNTIFS([Assigned To]:[Assigned To], OR(@cell = "Glenn Frazer", @cell = "Alan Jochim", @cell = "Anthony Double"))
Be well
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
@Scott Orsey @Paul Newcome Thanks for your help.
@Paul Newcome Somehow you knew exactly what I was trying to get to. Thanks for the added context...
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!