SUMIF or SUMIFS and how to use

Tony Exline
Tony Exline ✭✭
in Formulas and Functions

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)
image.png


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)
image.png


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:
· Share on FacebookShare on Twitter

Best Answer

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

    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.

    · Share on FacebookShare on Twitter

Answers

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

    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.

    · Share on FacebookShare on Twitter
  • Tony Exline
    Tony Exline ✭✭
    edited 05/18/21
    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!

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!