Forum Discussion
Solved
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...
Change the formula in J2 to
=SUMPRODUCT([Carbs]*([Date]=[@Date]))
or better, to
=SUMIF([Date],[@Date],[Carbs])
and fill down.
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?
I don't know - I'd have to see your revision of the workbook.
- It is the same one I sent to you.