Does anyone know how to convert this SUMIF Formula and REF a date from another sheet

Brandon SMG

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})))

Mike TV

@Brandon SMG

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}))

Brandon SMG

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.

Genevieve P.

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