SUMIF or SUMIFS and how to use
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!
Best 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.
Answers
-
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.
-
@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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!