COUNTIFS Formula to calculate the number of values in a column with multiple values and parent rows

SteCoxy

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!

Paul Newcome

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.

Paul Newcome

Try this:

=COUNTIFS({👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row), {Helper Column Range}, @cell = 1)

Paul Newcome

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.

SteCoxy

AMAZING Paul thank you! I had tried doing =COUNT(PARENT and wasn't getting anywhere so thank you for this!

SteCoxy

=COUNTIFS({Helper Column Range}, "1"), {👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row)

doesn't seem to be accepting this?

SteCoxy

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")

Paul Newcome

Try this:

=COUNTIFS({👥 UX&D People Planner 👥 Range 1}, HAS(@cell, Skill@row), {Helper Column Range}, @cell = 1)

SteCoxy

You deserve a medal Paul - this has worked thank you!!

Paul Newcome

Happy to help. 👍️