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

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

Find more posts tagged with

Manufacturing

Formulas

Project Management

Comments

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!

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

Sign in to join the conversation!

Quick Links

Trending discussions

Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…
count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…