SUMIF or SUMIFS and how to use

Options

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!

Tags:

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Tony Exline
    Tony Exline ✭✭
    edited 05/18/21
    Options

    @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
    Mark Cronk ✭✭✭✭✭✭
    Options

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!