COUNTIF Criterion between two ranges

Jill_newbie

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))

Jeff Reisman

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))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316499#Comment_316499

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")

COUNTIFS Function | Smartsheet Learning Center

Jill_newbie

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

Jeff Reisman

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))

Jill_newbie

Thank you!!!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316499#Comment_316499

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")

COUNTIFS Function | Smartsheet Learning Center

Valerie_WPA

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.