Countifs with criteria on 2 different ranges



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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!