I need help with a SUMIFS formula to calculate total hours for an assignee based on dates
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))
Best Answer
-
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.
Answers
-
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.
-
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.
-
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))
-
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?
-
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?
-
Yes! I think that would work perfectly.
-
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.
-
That worked! Thank you so much for the help. That's exactly what I needed. :)
-
Happy to help. 👍️
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
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!