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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!