COUNTIF Criterion between two ranges
I'm struggling to create a COUNTIF formula. I want it to count the number of instances that either equal 7, or are greater than 7 but less than 13. I keep getting #Incorrect Argument Set
=COUNTIF({Risk Register: Timeline Range 4}, 7, OR(>7, <13))
Best Answers
-
With your logic, you really want to use AND within your formula, along with "@cell ". You want to count all values that are greater than or equal to 7, AND less than 13, yes?
Try this:
=COUNTIF({Risk Register: Timeline Range 4}, AND(@cell >=7, @cell <13))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Change from COUNTIF to COUNTIFS, in order to use criteria from different ranges.
If your column for risk status is called "Risk Status," reference that column and set the criteria after your first range/criteria set in the formula:
=COUNTIFS({Risk Register: Timeline Range 4}, AND(@cell >=7, @cell <13), {Risk Register: Timeline Risk Status Range}, "OPEN")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Bonus: It would be even more ideal if this formula would consider only OPEN risks, which is listed in a different column than the exposure (which are the numbers I'm counting in my original formula). I can't even begin to understand how I would do that...
-
With your logic, you really want to use AND within your formula, along with "@cell ". You want to count all values that are greater than or equal to 7, AND less than 13, yes?
Try this:
=COUNTIF({Risk Register: Timeline Range 4}, AND(@cell >=7, @cell <13))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you!!!
-
Change from COUNTIF to COUNTIFS, in order to use criteria from different ranges.
If your column for risk status is called "Risk Status," reference that column and set the criteria after your first range/criteria set in the formula:
=COUNTIFS({Risk Register: Timeline Range 4}, AND(@cell >=7, @cell <13), {Risk Register: Timeline Risk Status Range}, "OPEN")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Should I be able to use this for time? I've extracted the time from the created column and would ideally like to count anything later than 11pm but before 6am.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 395 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
