SUMIFS Formula for Rows Assigned to Multiple Contacts

Using the Sheet Summary function this formula worked:

=SUMIFS({Budget Projected}, {Budget Account}, Variable@row, {Budget Assigned}, Employee#)

when the lined was assigned to only one contact, but now that I have lines with multiple contacts assigned to them it will not recognize even the one employee's name anymore. I tried adding a second field (Employee2) with the other employee's name, which I'd rather not do but it still wouldn't work.

Question:

How do I get the formula to work again pulling only "one" employee's name even though there are multiple-assignees on a row? Is there a possibility of it constructing the formula to pull only "current user" then it wouldn't need to specify an employee name in the Sheet Summary Field.

Find more posts tagged with

Formulas

Reports

Support

functionality

Conditional Formatting

Accepted answers

AmyM

@Mark Cronk

Thank you so much for the quick response and I think this might work, however, I guess I am unsure where to use the HAS in my current formula... how would that fit into here?

=SUMIFS({Budget Projected}, {Budget Account}, (Variable@row, {Budget Assigned}, Employee#)

Mark Cronk

Hi @AmyM

Try this:

=SUMIFS({Budget Projected}, {Budget Account}, Variable@row, {Budget Assigned}, HAS(@cell, Employee#)=1)

I assume that Employee# is your contact list.

Work?

Mark

All comments

Mark Cronk

Hi @AmyM ,

I think the HAS function is your solution. Link to info below. HAS searches for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found.

The Syntax is: HAS( search_range criterion )

search_range — The cell or cell range to search within.
criterion — The value you want to find.

https://help.smartsheet.com/function/has

Good luck.

Mark

AmyM

@Mark Cronk

Thank you so much for the quick response and I think this might work, however, I guess I am unsure where to use the HAS in my current formula... how would that fit into here?

=SUMIFS({Budget Projected}, {Budget Account}, (Variable@row, {Budget Assigned}, Employee#)

Mark Cronk

Hi @AmyM

Try this:

=SUMIFS({Budget Projected}, {Budget Account}, Variable@row, {Budget Assigned}, HAS(@cell, Employee#)=1)

I assume that Employee# is your contact list.

Work?

Mark

AmyM

@Mark Cronk

That worked!!!! Thank you so much, I wish I was a formula master - maybe one day. I very much appreciate your time and help.

-Amy

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.