When creating a histogram etc. by aggregating multiple answers like the image below,In the case of Excel, it is done by combining a pivot table and a simple calculation formula.(For details, please refer to the easy-to-understand article by Morgan Data Systems on the link.)
On the other hand, in Smartsheet, when you enable multiple selection in the column type drop-down list, multiple selection items will be entered in the cell of XNUMX, so put XNUMX for each selection as explained in the article above and pivot Since it is not so appropriate to aggregate with , I will explain how to aggregate the frequency of multiple selection items using the COUNTIF and HAS functions.
Smartsheet with multiple select items
In a form like the one above, Smartsheet will populate multiple selections in one cell when the column type dropdown list allows multiple selections.
There is no way to break this multiple selection item into individual selection items using a function and "divide each option and enter only 10"*, but here, a smart method using the HAS function is used. to introduce. (*Hint: Use CHAR(XNUMX), which is used to separate multiple selection items, to separate the options. If you do not understand, please contact info@cloudsmart.jp.)
Aggregation by HAS function
Specifically, it is calculated using the following formula.
For details, please refer to the help article link for each function.
Dare to explain the meaning of the function in Japanese,
One {choice} Refer to the "Multiple Choice" column range on another sheet such as "TV TV" "Radio Radio",
XNUMX For this range, each item in the range (@cell )But
XNUMX Select the row in the "Multiple Choice" column of the summary table (@row) has a choice of (HAS) or
XNUMX Aggregate if you have (COUNTIFS)
That's it.
View more as a sample in your dashboard
More in the Smartsheet experience cornerDashboard experience cornerI posted it as a sample, so please actually enter data in the form and check the operation. (Since the total results are not reflected immediately, please try refreshing (reloading) several times after entering.)
