SUMIFS to show daily hours worked
I have a report that is emailed daily that I upload into SS using Data Shuttle. I added Date Column and ID so it always add the row. It looks like this:
Now I need to create a formula that link these hours to our metric sheet so we can track their productivity. This is the last piece of the metric. For each row, I have the name listed as shown here and the date as shown here. I want to match the name and date then show Total Hours. I've tried a few things but can't get it to work.
Thoughts?
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Best Answer
-
Hi,
The formula is now working. I tested the COUNTIFS, and that worked. Then added the Hours column back and it returned 0. Tried with both SUMIFS and COUNTIFS. I either received a 0 or #INCORRECT. Took a screenshot of both sheets and was typing my answer. Went back to the sheet to grab another screenshot and it returned the correct number.
Not sure what happened, but it's working correctly. 😏
Thanks!
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Answers
-
=SumIfs({Total Hours},{Date}, either reference a cell with the date you want the sum for or put the date in using smartsheet date format ,{Name}, again either reference the cell with the name you are looking for or type the name within quotes)
That should work you will have to set up your references for total hours, date, and name. If you want to sum multiple specific dates you would need to use an OR formula to go along with it. I'm not as good at the OR formulas but there are others here if you provide more specifics of your output sheet I'm sure they could provide an OR formula for you
-
That was my initial try SUMIFS({external sheet total hours}, {external date}, date,{external name}, name@row). It returned 0. Rearranged the formula and either still 0 or #incorrect. Made sure the date format is the same. My date column on the source sheet is text. The date it is matching is a cell, not a date column. I thought maybe that would be the reason?
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
Hi @Darla Brown
Your SUMIFS structure is correct:
=SUMIFS({external sheet total hours column}, {external date column}, date, {external name column}, name@row)
If you're receiving 0, could it be that the values input for "date" or "name" aren't finding a match on the other sheet? To test this, can you try using a COUNTIFS with the exact same ranges and criteria?
=COUNTIFS({external date column}, date, {external name column}, name@row)
If this also returns 0, the formulas are unable to find matching rows in your other sheet. Let me know if you're using a Column Formula or a Cell Formula as well.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi,
The formula is now working. I tested the COUNTIFS, and that worked. Then added the Hours column back and it returned 0. Tried with both SUMIFS and COUNTIFS. I either received a 0 or #INCORRECT. Took a screenshot of both sheets and was typing my answer. Went back to the sheet to grab another screenshot and it returned the correct number.
Not sure what happened, but it's working correctly. 😏
Thanks!
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!