How To Set Up A SUMIFS Cross Sheet Formula that Considers a Date Range.
Sheet 1: Entry Log
Sheet 2: Allocated Hours
Hi, I have built a formula that sums the hours from the entry log and pulls them into allocated hours. however, the cell that I am pulling the sum into is date sensitive. I am trying to build a range around the formula that takes into account the (LOG Date) and the Cell Date i.e (Week 1) (Every 7 Days ) Please let me know if someone knows how to add to this and make it a SUMIFS
=SUMIF({Entry Log Range 1}, Description@row, {Entry Log Range 2})
Answers
-
Are you able to provide some screenshots?
-
so the coloum that says week 1, I'm trying to make the SUM time-sensitive, yet leave the criterion range the same.
-
You are going to have to change over to a SUMIFS.
=SUMIFS({Numbers}, {Description}, @cell = Description@row, {Dates}, date_criteria)
-
Ok great,from the {log date}, Date_Criteria} would you recommend a range ? or would a greater than equal to be sufficient? say if the last day of the week is 03/15/22 and the Log Date is 03/16/22 it would fall into Week 2, but if it was 03/15/22 it would still be week 1?
-
That is going to depend on what you need. If you need it to be between two dates then you would need to use a date range, but if it just needed to be greater than a certain date then you would just use that.
-
I feel like the range is a safer option seeing that the weeks are pre determined. Do you have and example of the syntax for the date range added to the Sumifs?
-
It would look somethign like this:
=SUMIFS({Numbers}, {Description}, @cell = Description@row, {Dates}, AND(@cell >= DATE(2022, 06, 12), @cell <= DATE(2022, 06, 18)))
-
=SUMIFS({Entry Log Range 2}, {Entry Log Range 1}, @cell = Description@row, {Entry Log Range 3}, AND(@cell <= DATE(2022, 3, 12), @cell >= DATE(2022, 3, 15)))
This is what the formula ended up as, it worked. However, the sum is 0 and I just put an entry in for a description of the 14th of march for 2 hours and still no changes.
-
What exactly is in Range 2?
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!