Pivot table with multiple items in 1 cell

Kevin Ikeda

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?

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.