COUNTIFS Formula to calculate the number of values in a column with multiple values and parent rows
Hello, I'm trying to help a colleague set up a metrics sheet that can help them report on the number of skills they have within their department.
There's a sheet of all the people in the department, which has a record of the person, their skills and what they are booked to work on.
• Each of the people records are assigned a set of multi-select skills (using a multi select dropdown column)
• Each of their sub tasks inherits the person’s skills field, so they can do a filter for people with particular skills and understand what they’re booked on.
The problem they have is that they don't want to include the skills inherited to the person’s sub tasks. It currently counts all skills in levels 2 and 3, but they only want to count level 2 - level 1 is just a team category/parent for structure - no relationship with skills.
The formula they are currently using is in the metric sheet is: =COUNTIFS({👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row))
The range is referencing the Skills multi select dropdown column where all the skills are listed.
Essentially, they want for the values in the level 2 parent rows to be counted, but not levels 1 or 3.
Here is a screenshot of the structure of the sheet:
From looking on some of the previous similar queries, I think this might need a helper column to identify the necessary rows, to be able to include these in the formula, but I'm not sure how to achieve this.
Any help is much appreciated!
Best Answers
-
You are correct that you would need a helper column to identify which rows you want to grab.
Insert a text/number column and use
=COUNT(ANCESTORS())
Then in the metrics formula you would include the new column as another range and the criteria would be 1.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Try this:
=COUNTIFS({👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row), {Helper Column Range}, @cell = 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You are correct that you would need a helper column to identify which rows you want to grab.
Insert a text/number column and use
=COUNT(ANCESTORS())
Then in the metrics formula you would include the new column as another range and the criteria would be 1.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
AMAZING Paul thank you! I had tried doing =COUNT(PARENT and wasn't getting anywhere so thank you for this!
-
=COUNTIFS({Helper Column Range}, "1"), {👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row)
doesn't seem to be accepting this?
-
Tried it like this too but it's saying incorrect argument:
=COUNTIFS({👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row), {Helper Column Range}, ="1")
-
Try this:
=COUNTIFS({👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row), {Helper Column Range}, @cell = 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
You deserve a medal Paul - this has worked thank you!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!