SUMIFS to show daily hours worked

Options

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

  • Darla Brown
    Darla Brown Overachievers
    Answer ✓
    Options

    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

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Options

    =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

  • Darla Brown
    Darla Brown Overachievers
    Options

    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

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Darla Brown
    Darla Brown Overachievers
    Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!