# Countif when counting cells in other sheet between 2 values

Hi ,

I am struggling a bit with using Countif to return a value from another sheet. A column contains numbers, I want to return number of cells that are for example >5<=10, greater than 5 but less than or equal to 10. For example =COUNTIF({Other Sheet Range 2}, >5 <= 10) .  Its does not seem to be working correctly as it returns more than it should. In my sheet if I use a filter function it returns 2 results, but in this formula I get 8.

Look forward to any suggestions.

Tags:

• I worked a little more and realized I needed to use combination of Countif and CountIFS.  Countif works with single value such as anything less than or anything greater than. Whilst Countifs for using data between 2 criteria such as: =COUNTIF({Other Sheet Range 2}, >1 < 6), where it would count cells with values greater than 1 or less than 6, so any of 2, 3, 4, 5 as a value.

• COUNTIFs({Other Sheet Range 2}, >5,{Other Sheet Range 2} <= 10)

• =COUNTIFS({Other Sheet Range 2}, AND(@cell >5, @cell <= 10))

This would also work. Since you are referencing the same range for all criteria, you only need to list it once and use an AND function to combine the criteria.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!