How to create a dashboard pie chart from a multi-select dropdown

I have a sheet that includes a multiselect dropdown of topic areas for users to select as many as are applicable as they fill out the form. In my dashboard, I want to have a pie chart that includes percentages of each topic area that are selected. However, the COUNTIF formula does not count anytime someone selects more than one option. I think I have to combined COUNTIF and HAS, but it has not worked for me yet.

So far, I have created a metric sheet, but need help in making the formula. The screenshot below is of my current formula (TA Tracker Range 1 is the original sheet I am pulling this from) Thank you in advance!!

Find more posts tagged with

Accepted answers

All comments

Pauline J

Hello @gstott

I think you will want to use a different method/formula. I created what I think you are trying to do using CONTAINS. The formula I used in the helper columns is:

=COUNTIF(Fruit:Fruit, CONTAINS("orange", @cell))

And then, changing "orange" to match the different option in each column.

You may need to add the helper columns at the end of your sheet — The result in the chart is below. I hope this was helpful! Please mark if this answered your question.

gstott

Confirming that I should not do this in a metric sheet, but rather in the original sheet?

Pauline J

@gstott You can do it in either — the helper clolumns could go in the original sheet (and you can hide them), or you can crate a new sheet and cross-reference into your original sheet. The formulas only have to be in a single row — you don't need to populate them down for every row. You could also try making a Summary Sheet field for each calculation and then create your chart from that on the dashboard.

Personally, if the original sheet is only updated/used by you, I'd add the helper columns to the original. I hope this works out well for you!

Pauline

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.