COUNTIFS CONTAINS & NOT FORMULA

Teresa Lai

Hi all, I'd like to know how I can modify this formula to make it work?

• Within row4 to row18, I want to count the number of "Complete" deliverables under Status Column for "Product Management" under Person Accountable Column. And the count needs to exclude the deliverables with a check-off under Inapplicable Column. How I can adjust the NOT formula below?

=COUNTIFS([Person Accountable]4:[Person Accountable]18, CONTAINS(@cell, "Product Management"), Done4:Done18, =1, NOT(CONTAINS(@cell, Product Management"), Inapplicable4:Inapplicable18, =1)

• Within row4 to row18, I want to count the number of "At Risk" and "Incomplete" deliverables under Status Column for "Product Management" under Person Accountable Column. And there is another condition to be excluded when the checkbox is checked off under Inapplicable Column. Do I nest the NOT in the two countifs formulas?

=COUNTIFS([Person Accountable]4:[Person Accountable]18, CONTAINS(@cell, "Product Management"), Status4:Status18, "At Risk") + COUNTIFS([Person Accountable]4:[Person Accountable]18, CONTAINS(@cell, "Product Management"), Status4:Status18, "Incomplete")

Paul Newcome

Try this for the first one:

=COUNTIFS([Person Accountable]4:[Person Accountable]18, CONTAINS(@cell, "Product Management"), Done4:Done18, @cell=1, Inapplicable4:Inapplicable18, @cell<>1)

For the second, I suggest writing a single COUNTIFS and using an OR function for the criteria.

=COUNTIFS(Status4:Status18, OR(@cell = "At Risk", @cell = "Incomplete"), [Person Accountable]4:[Person Accountable]18, ..............................)

and then finish out the rest of your range/criteria sets.