SUMIFS to show daily hours worked

Darla Brown

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

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!

Hollie Green

=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

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?

Genevieve P.

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

Darla Brown

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!