Using COUNTIFS

Phil Wightman

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

Genevieve P.

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

Genevieve P.

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

Phil Wightman

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.

Genevieve P.

Hi Phil,

Great! I'm happy to help. Hope you're having a good weekend!

Genevieve