Pivot table with multiple items in 1 cell

TLDR: Is there a way for the pivot table to add up multiple drop down options within a cell rather than treating multiple selections as 1 category?


Background:

I am setting up a quality check sheet in Smartsheet that requires an inspector to check all the reasons a part failed. he can select from 12 options in a drop down and he can select multiple options. Id like to have this go into a pivot table so we can see our top hitters, but the problem is the pivot table does not split the multiple selections within cells.

for example, if we have failures with cell 1 having failure A, Cell 2 failure B, cell 3 with failure A & B, smart sheet doesnt sum a pivot table with 2 failures for A and 2 for B. Instead it says there is 1 for A, 1 for B, and 1 for AB. This isnt very helpful for my analysis. Is there a way around this?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can create your own table


    Failure..........Count

    A........................f

    B........................f


    And use a formula such as:

    =COUNTIFS({Other Sheet Failure Column}, CONTAINS(Failure@row, @cell))


    The bold portion within the formula will count how many times that particular failure appears within the range regardless of whether or not other selections are made in the same cell.

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!