Does anyone know how to convert this SUMIF Formula and REF a date from another sheet
Hi,
Were looking to take this active formula which is currently working and replace the date ranges from another sheet (Cross Sheet Reference) . Does anyone know if this is possible ? Because it is time consuming to replace the date range on each formula, we want to be able to edit the date range from another sheet.
=SUMIFS({Entry Log_Gestetner Range 1}, {Entry Log_Gestetner Range 2}, @cell = $Description@row, {Entry Log_Gestetner Range 3}, AND(@cell >= DATE(2022, 7, 19), @cell <= DATE(2022, 7, 26)))=SUMIFS({Entry Log_Gestetner Range 1}, {Entry Log_Gestetner Range 2}, @cell = $Description@row, {Entry Log_Gestetner Range 3}, AND(@cell >= DATE(2022, 7, 19), @cell <= DATE(2022, 7, 26)))
Log_Range 1 = Time in Hours
Log Range 2= Description
Log Range 3 = Date of Entry
We attempted this formula but its coming back as Incorrect Argument Set
=SUMIFS({Entry Log_Gestetner Range 1}, {Entry Log_Gestetner Range 2}, @cell = Description@row, {Entry Log_Gestetner Range 3}, AND(@cell >= DATE({Date Metrices (Vertical) Range 5}), @cell <= DATE({Date Metrices Range 1})))
Answers
-
Try this:
=SUMIFS({Entry Log_Gestetner Range 1}, {Entry Log_Gestetner Range 2}, @cell = Description@row, AND({Entry Log_Gestetner Range 3} >= {Date Metrices (Vertical) Range 5}, {Entry Log_Gestetner Range 3} <= {Date Metrices Range 1}))
-
Unfortunately that did not work. The two Date formulas are basically the Pay Period to which the hours are applied. Rather than constantly change the dates for every pay period column, I would like a formula to basically add 7 days each time I copy the formula over to the next column.
-
Hi @Brandon SMG
What I would do in this instance is set up a cell at the top of each column that identifies the Day and Month for that current column's pay period. I would suggest having this as a consistent MM/DD format.
Then you can reference that cell in your DATE function to extract the correct number, so as you drag the formula over it will update the Day and Month in your Date.
For example instead of:
DATE(2022, 07, 19)
You could have:
DATE(2022, VALUE(LEFT([Column Name]$1, 2)), VALUE(MID([Column Name]$1, 4, 2)))
You can use this same formula and + 7 on to it for the second Date reference.
=SUMIFS({Entry Log_Gestetner Range 1}, {Entry Log_Gestetner Range 2}, @cell = $Description@row, {Entry Log_Gestetner Range 3}, AND(@cell >= DATE(2022, VALUE(LEFT([Column Name]$1, 2)), VALUE(MID([Column Name]$1, 4, 2))), @cell <= DATE(2022, VALUE(LEFT([Column Name]$1, 2)), VALUE(MID([Column Name]$1, 4, 2))) + 7))
Let me know if that makes sense and will work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!