Using COUNTIFS
Hi,
I am using the following formula to count those occasions when CSOD (unit) is impacted by CFCD (unit) and the status of the impacts is either 'conceptual' or 'complete' and it does not work. If I limit it to only counting 'conceptual' it works. What I am missing to count more than one status?
=COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "conceptual", {Interdependencies and impacts register Range 3}, "complete")
Thanks
Phil
Best Answer
-
Hi Phil,
The reason it's not working is that you're looking for two criteria separately within the same range, Range 3. Try adding your two COUNTS together... one for "conceptual" and one for "complete".
=
COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "conceptual")
+
COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "complete")
Full Formula:
=COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "conceptual") + COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "complete")
Let me know if that works!
Cheers,
Genevieve
Answers
-
Hi Phil,
The reason it's not working is that you're looking for two criteria separately within the same range, Range 3. Try adding your two COUNTS together... one for "conceptual" and one for "complete".
=
COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "conceptual")
+
COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "complete")
Full Formula:
=COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "conceptual") + COUNTIFS({Interdependencies and impacts register Range 2}, "CSOD", {Interdependencies and impacts register Range 4}, "CFCD", {Interdependencies and impacts register Range 3}, "complete")
Let me know if that works!
Cheers,
Genevieve
-
Hi @Genevieve P , thank you so much for taking the time to look at this. You fixed it! Yeah and happy Friday wherever in the world you are.
-
Hi Phil,
Great! I'm happy to help. Hope you're having a good weekend!
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!