I need help with a SUMIFS formula to calculate total hours for an assignee based on dates

JessNH

I'm working with a sheet that has a start date and due date, a column for assignee name, and a column for Hours/Week they're working on a project. I'm trying to get a formula to calculate the total hours an assignee is working throughout the sheet between two dates. I'm currently trying the formula below but it's brining back 0. Any thoughts on what I'm doing wrong?

=SUMIFS([Hours/Week]:[Hours/Week], [Assigned To]:[Assigned To], "Angie Smith", [Start Date]:[Start Date], >=DATE(2022, 10, 1), [Due Date]:[Due Date], <=DATE(2022, 10, 31))

Paul Newcome

Ok. In that case the logic would be

Due Date is greater than or equal to Oct 1 and Start Date is less than or equal to Oct 31.

=SUMIFS([Hours/Week]:[Hours/Week], [Assigned To]:[Assigned To], @cell = "Angie Smith", [Start Date]:[Start Date], @cell<=DATE(2022, 10, 31), [Due Date]:[Due Date], @cell>=DATE(2022, 10, 1))

This will grab anything that has any overlap in the month of October.

Paul Newcome

Are you able to provide a screenshot of the source data with sensitive/confidential data blocked out? It seems like your formula should be working.

JessNH

Yes, thanks! We're still developing this tool so maybe it's because it's not setup in the best way to capture that info? Appreciate any feedback and thoughts!

I'm currently testing that formula in the bottom of the sheet in an open cell. I haven't thought through where that would fit into the sheet overall.

Paul Newcome

Let's try adding some "@cell" references as below. Also... Do you have a screenshot of any rows that actually match the criteria so we can compare against the formula?

How is the [Hours/Week] column populated?

=SUMIFS([Hours/Week]:[Hours/Week], [Assigned To]:[Assigned To], @cell = "Angie Smith", [Start Date]:[Start Date], @cell>=DATE(2022, 10, 1), [Due Date]:[Due Date], @cell<=DATE(2022, 10, 31))

JessNH

The @cell didn't work but I appreciate it!

This screenshot below in lines 80 and 82 has an example of the assignee Angie (AE), the hours/week (which is a number I put in by hand), and the start and end date of her work during each phase in that project.

I think what I'm realizing is this is not the right formula to capture what I need with the way my sheet is setup with state date and due date. Any thoughts on a better formula here?

Paul Newcome

Ah. There it is. You are only grabbing rows where the Start Date and Due Date are BOTH in October, but the data does not match your formula (start and due dates in different months). We are going to need to try a different approach. Would it work for your needs if the dates spanned October? So something starting in June and due in December would count?

JessNH

Yes! I think that would work perfectly.

Paul Newcome

Ok. In that case the logic would be

Due Date is greater than or equal to Oct 1 and Start Date is less than or equal to Oct 31.

=SUMIFS([Hours/Week]:[Hours/Week], [Assigned To]:[Assigned To], @cell = "Angie Smith", [Start Date]:[Start Date], @cell<=DATE(2022, 10, 31), [Due Date]:[Due Date], @cell>=DATE(2022, 10, 1))

This will grab anything that has any overlap in the month of October.

JessNH

That worked! Thank you so much for the help. That's exactly what I needed. :)

Paul Newcome

Happy to help. 👍️