Forum Discussion
Formula changes when a row is inserted
Change the formula in J2 to
=SUMPRODUCT([Carbs]*([Date]=[@Date]))
or better, to
=SUMIF([Date],[@Date],[Carbs])
and fill down.
Change the formula in J2 to
=SUMPRODUCT([Carbs]*([Date]=[@Date]))
or better, to
=SUMIF([Date],[@Date],[Carbs])
and fill down.
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.
- 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.
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.
- 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?
