Create SUMIFS formula with a contact criteria in a multiple contacts range

Rdewitte

Hi,

I am looking for the right way to build my formula that makes a sum of a number column with a contact criteria in a multiple contacts range.

Example:

=SUMIFS({numberRange with hours}; {Criteria Range with multiple contacts}; Contact Name; {Range with Statuscode}; "In Progress")

Check also the screenshot below.

If I want the total hours (last column) spend by 'Roy de Witte' I want to include also the hours of the rows where 'Roy de Witte' is part of a cell with multiple contacts.

Above formule returns at this moment only the hours of the rows with only 'Roy de Witte' (= a value of 20 hours. I want to see the a value returned of 24 hours.

 

Thanks in advance!

Roy

 

 

Andrée Starå

Hi Roy,

At the moment you would need to use the Find function for it to work but Smartsheet is investigating how to make it more straightforward in the future.

Would Find work in the meanwhile?

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Rdewitte

Hi Andrée,

Thanks for your quick reply! An extra column with a FIND function works in the meantime.

Hoping for an easier solution in the future

Greetings,

Roy

 

Andrée Starå

Happy to help!

I don't think we will have to wait too long for a better solution.

Have a fantastic day!

Best,

Andrée

Paul Newcome

You shouldn't need an extra column for the FIND function. You can build it right into your SUMIFS

 

=SUMIFS([Sum Column]:[Sum Column], [Contact Column]:[Contact Column], FIND("John Smith", @cell) > 0)

Rdewitte

Hi Paul,

 

That was exactly what I was looking for.

Works like a charm!

Many thanks,

 

Roy

Paul Newcome

Excellent! Happy to help. 

Kyle Keever

@Paul Newcome Hoping you can help me. I'm trying to do something similar to above and am wanting to sum a column with sum ifs with a single contact from a multi contact as a criteria cell but it's not working.

=SUMIFS({Job Health Check check count}, {Job Health Check Up Emp not safe}, HAS({Job Health Check Up Emp not safe}, contact51), {Job Health Check Up Date reported}, >=[Column6]26)

Thanks!

KK

Kelly Moore

Hey @Kyle Keever

The @cell reference tells smartsheet to evaluate every cell in a range.

Try this

=SUMIFS({Job Health Check check count}, {Job Health Check Up Emp not safe}, HAS(@cell, contact51), {Job Health Check Up Date reported}, >=[Column6]26)

Kelly

Kyle Keever

That worked!! Thanks you very much @Kelly Moore