Countifs with criteria on 2 different ranges

Brimmunigen

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)

James Keuning

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.

Brimmunigen

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