COUNTIFS Function with an "Or" function included?

Jenna2424

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!

Nick Korna

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

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:

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Nick Korna

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!

Jenna2424

@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.

Nick Korna

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:

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Jenna2424

@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!