COUNTIFS and CONTAINS

anna2121

Hi! I need some guidance for a count analysis of our RAID Log (Risks, Issues, Decision). In the RAID Log source sheet, we have a column where the team selects the Project(s) the risk or issue is tied to. The Project column is set up as multi-select. In a separate Count RAID sheet, I'm using this formula: =COUNTIFS({Risk & Issue Log Range 1}, CONTAINS(Project@row, @cell), {Risk & Issue Log Range 2}, <>"Closed", {Risk & Issue Log Range 3}, "Risk")

I included the CONTAINS function to factor in line items with multiple projects selected. The issue with this is that it is double counting some items. However, if I take out CONTAINS, it won't count the line items that have selected multiple projects. Do you have suggestions on how to approach this correctly? Appreciate it.

Brett Wyrick

Hey Anna.

To quote Doc Holliday: "I believe the DISTINCT function will be your huckleberry."

Okay, so Doc never said this - but I do think DISTINCT will work for this.

With you using so many cross-sheet references, it's hard to tell where the DISTINCT function ought to go, but it likely should go right after COUNTIFS.

Take a read through the DISTINCT function here and let me know if you think this will work for you!

anna2121

Hmm might be on the right path? Hard for me to say. In this screenshot, we have 3 projects that have the same risk associated with them. What is happening with my current formula is that it's counting that as 3 risks, whereas I'd like to count it as one. With this knowledge, do you still think DISTINCT would help me accomplish this? I'm not sure how to incorporate it in to the formula.. Do I still keep CONTAINS function?

I get an error when I try this.. =COUNTIFS(DISTINCT(COLLECT({02 Firefly Risk, Issue & Decision Range 1}, {02 Firefly Risk, Issue & Decision Range 1}, Project@cell), 02 Firefly Risk, Issue & Decision Range 1}, CONTAINS(Project@row, @cell), {02 Firefly Risk, Issue & Decision Log Range 2}, <>"Closed", {02 Firefly Risk, Issue & Decision Log Range 3}, "Risk")

Genevieve P.

Hi @anna2121

Since you're looking at a multi-select column, I would actually recommend using the HAS function instead of Contains. However the output should be the same:

=COUNTIFS({Risk & Issue Log Range 1}, HAS(@cell, Project@row), {Risk & Issue Log Range 2}, <>"Closed", {Risk & Issue Log Range 3}, "Risk")

If you have each Project listed separately on your destination sheet, then this formula will count 1 per Project since each of these projects has "Risk" next to them.

However it sounds like you only want the Total number of Risks to be associated with the number of rows that say "Risk", regardless of how many projects are selected in the multi-select column, is that correct? Could you have a separate total line in your destination metric sheet, that ignores the Project selections, like so:

=COUNTIFS({Risk & Issue Log Range 2}, <>"Closed", {Risk & Issue Log Range 3}, "Risk")

Let me know if I've misunderstood what you're looking to count.

Cheers,

Genevieve