SUMIFS Formula for Rows Assigned to Multiple Contacts

AmyM

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.

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

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