Countif when counting cells in other sheet between 2 values

Vasiles Kiosses

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.

Vasiles Kiosses

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.

L_123

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

Paul Newcome

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