Formula changes when a row is inserted

[wcstarks]

When I insert a row anywhere, but normally between row 2 and row 3 to add a new row to fill to keep the most current first. This formula which should be as shown:

=SUMPRODUCT($F$2:$F$49*(INT($A$2:$A$49)=A6)), the inserted row changes the $2 to $1 in both places. It used to work OK as long as I left a dummy row as row 2. But not now after I inadvertently deleted dummy row 2 it stopped working correctly.  How can I get this to insert and keep the formula as shown.

The formula is in Sheet is Q1, Column J. The sheet is formatted as a table.

[#2 · Accepted solution]

@wcstarks 

Change the formula in J2 to

=SUMPRODUCT([Carbs]*([Date]=[@Date]))

or better, to

=SUMIF([Date],[@Date],[Carbs])

and fill down.

[#3]

Thank you for your response. Would you please explain how you are able to use [carbs] in your rendering of the function to help me better understand? And please help me understand how the differing syntaxes produce similar results, that I may learn.

[#4]

I just replaced my formula with your second formula and attempted to insert a row, and it produced the original erroneous formula: =SUMPRODUCT($F$1:$F$51*(INT($A$1:$A$51)=A4)) Somehow, Excel has this formula in memory and uses it instead of the formula in the column. So, I guess the real issue is how to get Excel to forget this erroneous formula when inserting a new row and use the current formula for that column.

I also notice that I get a notification of an inconsistent formula, even though the formula is the same in each row. Excel must be comparing the new formula with the one it has in memory.

[#5]

@wcstarks 

When data are formatted as a table, you can use so-called structured references.

In a formula in the table itself, you can refer to all data rows in a column as [ColumnName].

For example [Date] refers to A2:A49, but if you insert a row in the table, [Date] will refer to A2:A50.

And you can use [@ColumnName] to refer to the cell in the specified column in the same row as the cell with the formula.

For example in J2, [@Date] refers to A2 and in J10, [@Date] refers to A10.

See the attached version of the workbook. Try inserting a new row in the table.

[#6]

I just downloaded your revision of the book and I see it works fine. Why did my modification in my version not work correctly, while it does in your version?

[#7]

@wcstarks 

I don't know - I'd have to see your revision of the workbook.

[#8]

It is the same one I sent to you.

[#9]

@wcstarks 

But the one attached to the first post in this discussion has not been modified.

[#10]

I guess I don't understand. Did you not make a copy of the one I uploaded. Then it would be a copy of the one which was having the issue described. I guess it doesn't matter now. You got it working on the one you sent me. When I enter a new function or modify a function, I then select the green check mark to save the function. It usually updates the column automatically. The then that perplexes me is, it will then ask to update the column, after it is already done it. Other times I will get the message of inconsistent functions, even though I cannot see any difference from one cell to the next.