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