Countif when counting cells in other sheet between 2 values

Vasiles Kiosses
Vasiles Kiosses ✭✭✭
edited 12/09/19 in Formulas and Functions

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:

Comments

  • 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
    L_123 ✭✭✭✭✭✭

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

  • Paul Newcome
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!