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.
-
Try this:
=COUNTIFS({👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row), {Helper Column Range}, @cell = 1)
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.
-
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)
-
You deserve a medal Paul - this has worked thank you!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!