Countif when counting cells in other sheet between 2 values

Vasiles Kiosses · 03/19/19

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 · 03/19/19

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 · 03/19/19

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

Paul Newcome · 03/21/19

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

Formulas and Functions

Formulas and Functions

Countif when counting cells in other sheet between 2 values

Comments

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA