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.

Paul Newcome

Happy to help. 👍️

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.

Teresa Lai

Hi Paul, thank you a lot!

The first one works perfectly. And the second one I followed the logic of first one to complete the formula as:

=COUNTIFS(Status4:Status18, OR(@cell = "At Risk", @cell = "Incomplete"), [Person Accountable]4:[Person Accountable]18, CONTAINS(@cell, "Product Management"), Inapplicable4:Inapplicable18, @cell <> 1)

They both work now! Thank you for the prompt reply!

Paul Newcome

Happy to help. 👍️