Help with Totaling Against Options in Multi Select Drop Down in a Report?

Hello! Here is my scenario -

I have a set up like this in my sheet (this is of course dummy data just to give a visual of the sheet structure) -

I am trying to create a report grouped by the "Assigned To" so that I can summarize the # Hours each person is assigned, and then have a chart widget from that on a dashboard.

I know I can't group by a multi select column in a report, so have been trying to figure out a workaround with no luck. We have tried splitting it out into multiple rows so that we have a single select drop down, but it makes for a lot of extra manual work when building out our timelines.

Appreciate any advice!!

Find more posts tagged with

Sheets

Reports

Accepted answers

All comments

KPH

How many options are there in the Assigned To column? I was wondering if we could split them into multiple columns (using formulas, not manually). One column for Design, one for copy, one for PM and then fill each with the hours for each of them.

Also, how you do the math for #Hours. Is it duration divided by number of entries in the Assigned To field?

chlod24

https://community.smartsheet.com/discussion/comment/420425#Comment_420425

Hi, thanks for responding! I'm not sure that would work, there would be a lot of columns! We'll have 10-20 options on the dropdown column. And those options could change.

If 2 selections are against a row, the hours for them would be the same, so no math needed there.

But the end product I'm looking for is to be able to create a chart widget on a dashboard that will show me total hours against each option in the dropdown, so to get that I need to be able to create a report that can summarize each

KPH

How about creating a little summary sheet and using that to create the graph, rather than a report?

You could create a table like this:

You would need to enter each name in the Assignee column and the following formula for Total effort.

=SUMIF({cross sheet reference to your assigned to column}, HAS(@cell, Assignee@row), {cross sheet reference to your duration column})

You will need to replace the parts within {} with references to your original sheet (let me know if you need help with that).

This could then be used to create a graph like this:

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.