COUNTIFS Function with an "Or" function included?
Hi all. I am currently trying to create a COUNTIF function that has 2 required parameters, and then 4 "or" parameters.
Basically, the formula should count if the RAID Log box is checked AND if the RAID Item is Risk. Then, it should count if the item's status is "new" OR "in progress" OR "deferred" OR "ready for close".
I've tried a few different combinations but keep getting an "unparseable" message.
If anyone has a potential formula for this situation, please let me know.
Thank you in advance!
Best Answers
-
Hi @Jenna2424,
Your formula should be something like this:
=COUNTIFS([RAID Log]:[RAID Log], 1, [RAID Item]:[RAID Item], "Risk", Status:Status, OR(@cell = "New", @cell = "In progress", @cell = "Deferred", @cell = "Ready for close"))
Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!
-
It is referring to cells within that column.
If you were doing a single criteria (as opposed to multiple within the OR) you would just use that criteria in quote marks, but if you are doing multiple with an OR, you need to use the @cell reference to prevent an #INVALID OPERATION error coming up. Example:
The alternative would be to do 4 separate COUNTIFS without the OR included and add them together, but that would be much more long winded so using an OR with the @cell references is much quicker and easier.
For a bit more reading:
Answers
-
Hi @Jenna2424,
Your formula should be something like this:
=COUNTIFS([RAID Log]:[RAID Log], 1, [RAID Item]:[RAID Item], "Risk", Status:Status, OR(@cell = "New", @cell = "In progress", @cell = "Deferred", @cell = "Ready for close"))
Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!
-
@Nick Korna Thank you so much! One follow-up question - the OR function in your formula looks like it is referring cells rather than an entire column - am I correct in that? I'd like it to reference the full Status column.
-
It is referring to cells within that column.
If you were doing a single criteria (as opposed to multiple within the OR) you would just use that criteria in quote marks, but if you are doing multiple with an OR, you need to use the @cell reference to prevent an #INVALID OPERATION error coming up. Example:
The alternative would be to do 4 separate COUNTIFS without the OR included and add them together, but that would be much more long winded so using an OR with the @cell references is much quicker and easier.
For a bit more reading:
-
@Nick Korna Oh, I see! Thank you so much. The formula worked and I better understand the OR function within it now. Thank you for the info + additional assistance!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!