# COUNTIF Criterion between two ranges

Options

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

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

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

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

Thank you!!!

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!