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.
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 522 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!