SUMIF or SUMIFS and how to use

Tony Exline

I need to obtain a running total of days for an inspector for a given month in a column “Days Per Inspector”.

I have the following Sheets & Columns:

Inspector (Personnel) Sheet

• Days Per Inspector (SUM – Number)
• Inspector Contact (Name – Contact List)

Inspections Sheet

• Inspector 1 (Name – Contact List)
• Inspector 2 (Name – Contact List)
• Inspector 3 (Name – Contact List)
• Duration (Number)
• Planned Month (Month Number 1 to 12)

The Inspector Contact (name)  can appear in any of the 3 inspector slots, but only once per row. To get started I am only trying to match Inspector 1 (but I need all 3).

Duration is the number of days I am attempting to total on the Inspector Sheet: Days Per Inspector.

Planned Month is the Month that I want the total number of days on inspections.

I’m new to Smartsheet and have limited experience with Excel and relational databases so I’m trying to get my head around which formula to use here (and how!).

SUMIF?

SUMIFS?

I appreciate any and all input!

Mark Cronk

Hi Tony,

Try in your Date Per Inspector column:

=SUMIFS({Duration:Duration}, {[all inspectors scheduled]:[all inspectors schedule]}, HAS(@cell, [Inspector Full Name]@row, {[planned month]:[planned month]}, @cell = month(today()))

Replace the ranges in {} with external sheet ranges to the named columns. Adjust the month to be the month you want reported. What I input will return days for the current month.

The HAS function can be a bit tricky. It looks for exact matches so the names you use have to be identical in both sheets.

Any luck?

Mark

Mark Cronk

Hi Tony,

Try in your Date Per Inspector column:

=SUMIFS({Duration:Duration}, {[all inspectors scheduled]:[all inspectors schedule]}, HAS(@cell, [Inspector Full Name]@row, {[planned month]:[planned month]}, @cell = month(today()))

Replace the ranges in {} with external sheet ranges to the named columns. Adjust the month to be the month you want reported. What I input will return days for the current month.

The HAS function can be a bit tricky. It looks for exact matches so the names you use have to be identical in both sheets.

Any luck?

Mark

Tony Exline

https://community.smartsheet.com/discussion/comment/291241#Comment_291241

@Mark Cronk - I ended up using yours as a model but couldn't get the "HAS" function to work correctly, so I just added (with a +) a second and third SUMIFS string as follows:

=SUMIFS({Duration}, {Planned_Month}, [Planned Month - Temp]#, {Inspector_1}, [Inspector Contact]@row) + SUMIFS({Duration}, {Planned_Month}, [Planned Month - Temp]#, {Inspector_2}, [Inspector Contact]@row) + SUMIFS({Duration}, {Planned_Month}, [Planned Month - Temp]#, {Inspector 3}, [Inspector Contact]@row)

I also made the Planned Month dynamic by adding a summary field to the sheet - Planned Month - Temp. I then added another column to keep a running total of hours with a similar formula:

=SUMIFS({Duration}, {Inspector_1}, [Inspector Contact]@row) + SUMIFS({Duration}, {Inspector_2}, [Inspector Contact]@row) + SUMIFS({Duration}, {Inspector 3}, [Inspector Contact]@row)

Might not be pretty (or the most efficient way), but both work like a champ!

Thanks for the guidance!

Mark Cronk

Nice work. Glad you found a solution. The dynamic month is a great touch. Thank you for contributing to the Community.

Mark