Countifs with criteria on 2 different ranges
hello,
I have formulas in a metrics sheet looking at information in a second sheet. I want to count the number of items in the second sheet that meet both a criterion of a checkbox being checked in one column (SS range 2) and the value in another column (SS range 3) being between 2 values (in this case, between 0 and the number in the header row of the metrics sheet. Each portion of the countif formula below works independently and gives me the right number, but when I combine into a counifs formula with 2 criteria, the number I get is incorrect. Note: the numbers in SS range 3 are either entered or calculated percentages, depending on the row. Any clues on what I have done wrong?
COUNTIFS({SS Range 2}, 1, {SS Range 3}, >0 <= [Task Name]@row)
Answers
-
I am confusing by the first \> in this statement: >0 <= [Task Name]@row
Do you just mean 0 <= [Task Name]@row?
I also don't know what you mean by "number in the header row of the metrics sheet"
When I face a problem like this, I create two checkbox fields and configure them so that the condition I am looking for returns TRUE. Then I use those as the criteria for my COUNTIFS. Because that way I isolate two confusing functions.
-
The logic is >0 AND <= [Task Name]@row (or between 0 and [Task Name]@row) and it works as written as a standalone countif function. It is only in combination with the second logical argument that it fails (no error, just wrong answer).
The second check box will not work, because it is a moving target: {SS Range 3} value changes over time, and we are evaluating if it now is between 0 and [Task Name]@row. Thanks for the idea of 2 checkboxes, though. It could have worked in another situation
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!