Report with Multiselect Contact Field

I'm playing around with reports as I'm new with it and in our smartsheet we have a contact field for assignments. This field is a multivalue option as there is a manager who on certain items gets assigned as well for visibility.

However, when trying to create a report and filter on the number of rows per assigned person (in the contact field), it won't show the field in question as an option to group by.

I did test breaking it back to a single value field only and it does work.

Is there a limitation on grouping if multiple values ?

I had hoped it would just count those as a separate item in the grouping

For ex:

Person A (by themselves)- 6 tickets

Person B (by themselves) - 8 Tickets

Person A/B (together) - 10 tickets

Find more posts tagged with

Reports

Accepted answers

All comments

David Tutwiler

There is a limitation on multi-select fields and grouping in reports. I don't believe it is available.

Depending on how many people you have in the assignments, you could make Sheet Summaries on the sheet and pull those into a report. Instead of Row Report you would select Summary Report.

The formula for finding this would involve a COUNTIF or COUNTIFS and a CONTAINS formula. Something like:

=COUNTIF([Assigned To]:[Assigned To], CONTAINS("Person A", @cell))

tovythomas

https://community.smartsheet.com/discussion/comment/440196#Comment_440196

Thanks @David Tutwiler , but how would i grab both people together. For example if John Doe/Jane Doe are both assigned to the same ticket (row), how would you do the combination formula.

I have a formula like this currently

=COUNTIFS([Billing Specialist Assigned]:[Billing Specialist Assigned], "John Doe") which gives me a value of 3 which is the number of rows they are in by themselves (not including multiselect).

If i add a + plus sign and another person ("Jane Doe") with another countifs, it will give me their total value together where they are assigned to a row by themselves. I'm trying to only pull when the 2 are together in that case.

I tried (assuming i did it right) your suggestion like this for even 1 value

=COUNTIF([Billing Specialist Assigned]:[Billing Specialist Assigned], CONTAINS("John Doe", @cell)) but it just gives me a value of 0 not unparseable as i would have thought if i messed up the formula.

David Tutwiler

You'll need to wrap your Contains with an AND and put both contains formulas as the parameters of the AND function.

Since it is a contact column, you might have to use the email address in the Search For part of the Contains function.

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.