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!

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!