Summary Sheet & Filter based on Form Responses with Multi-Select Dropdown

Hi all! Hoping for some assistance with a summary formula pulling in criteria from a multi-select dropdown response form...

I have a dashboard that has a form embedded in it to allow the user to enter their desired criteria (see below screenshot) and then the bottom of the dashboard pulls in the metrics using the form response as a filter (based on the main sheet).

The user must be able to select multiple Job Levels. The form is set up as a multi-select dropdown. The response feeds into a Response Sheet (see below). For example, the latest entry has both M1 and M2 selected as the Job Level.

From here, I used a Summary Formula on the main sheet (where the Employee's Job Level, SVP, etc. are all listed). For Job Level, I used the formula: =INDEX({Filter Criteria - Job Level}, Row#). This is working to display both selected Job Levels.

I then have a checkbox column ("Filter Criteria") on the main sheet to see only the rows that meet this Sheet Summary criteria.

This is where I am stuck!

The Sheet Summary is returning both Job Levels correctly, but for the filter column, there are no employees that are BOTH Job Levels. I need this Column to search for either M1 or M2 or whatever Job Levels are selected together. I tried adjusting the FIND portion of the formula (shown below) to HAS, CONTAINS, OR but none were working as I had anticipated. The formula is currently:

=IF(AND(FIND(SVP#, SVP@row ) > 0, FIND([Job Level]#, [Job Level]@row ) > 0, FIND(Location#, Location@row ) > 0, FIND([Date Range]#, [Date Range]@row ) > 0), 1)

Is there some way to incorporate an "OR" in there for the Job Levels so I can filter to see both M1 and M2? The only other alternative I can think of is to have separate entries for each desired Job Level, but I can see that getting lengthy (we have 21 Job Levels in total).

Any insight is appreciated!

Find more posts tagged with

Formulas

Sheet Summary

INDEX

Multi-Select Dropdown

if formula

Accepted answers

Paul Newcome

You have the CONTAINS flipped around backwards from my example. You should have

CONTAINS([Job Level]@row, [Job Level]#)

You have it the other way around.

All comments

Paul Newcome

Try a CONTAINS like so:

=IF(AND(………………., CONTAINS([Job Level]@row, [Job Level]#), …………….), 1)

Elizabeth N

Thank you @Paul Newcome. I tried adjusting it to that and am still getting "0" that meet the filter criteria (it should be returning 225 employees based on the Job Level and SVP criteria submission on the form).

Do you think it has something to do with the Form Response being multi-select, but the Sheet Summary being text? I don't see any way to have the Sheet Summary display in the same format as multi-select?

Paul Newcome

You have the CONTAINS flipped around backwards from my example. You should have

CONTAINS([Job Level]@row, [Job Level]#)

You have it the other way around.

Elizabeth N

Holy moly @Paul Newcome! Thank you for catching that — it works now :) Thank you so much!

Paul Newcome

Happy to help. 👍️

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.