Sumifs Formula with a Contain

I am trying to get the sum of hours worked IF the "Office" Column = Administrative Solutions. The Office column is a multi-select dropdown.

=SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, CONTAINS("Administrative Solutions", @cell"))

Any help would be appreciated! Thank you!

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @Maria Watters

Since you're looking in a multi-select column, try using the HAS function. It's specifically designed to search for one selection in a multi-select cell.

=SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, HAS(@cell, "Administrative Solutions"))

Let me know if this works for you! If not, it would be helpful to know what error or issue you're seeing with the formula, and a screen capture of the sheet you're referencing (but please block out any sensitive data).

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Maria Watters

Since you're looking in a multi-select column, try using the HAS function. It's specifically designed to search for one selection in a multi-select cell.

=SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, HAS(@cell, "Administrative Solutions"))

Cheers,

Genevieve

Maria Watters

Thank you so much! That did work! What is the difference between HAS and CONTAINS. I believe I have used contains before on a multi-select drop down as well.

Genevieve P.

No problem, I'm glad it worked for you!

CONTAINS will see if a cell contains the text. So, say you were searching for the word "App" but one of the options was also "Apple". It will see the app in Apple and count that as 1.

HAS looks for an EXACT match, start-to-finish. Let me know if that makes sense or not! I'd be happy to post screen captures to illustrate the difference.

Cheers,

Genevieve

Maria Watters

@Genevieve P. Thank you so much! This is VERY Helpful.

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.