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

SteCoxy
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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!