COUNTIFS and CONTAINS
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.
Answers
-
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!
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
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")
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!