Jan 30 2022 07:08 AM - edited Jan 30 2022 07:10 AM
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.
Jan 30 2022 07:21 AM
SolutionChange the formula in J2 to
=SUMPRODUCT([Carbs]*([Date]=[@Date]))
or better, to
=SUMIF([Date],[@Date],[Carbs])
and fill down.
Jan 30 2022 08:15 AM
Jan 30 2022 08:27 AM - edited Jan 30 2022 08:34 AM
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.
Jan 30 2022 08:33 AM
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.
Jan 30 2022 09:07 AM
Jan 30 2022 01:18 PM
I don't know - I'd have to see your revision of the workbook.
Jan 30 2022 01:37 PM
But the one attached to the first post in this discussion has not been modified.
Jan 30 2022 04:11 PM
Jan 30 2022 07:21 AM
SolutionChange the formula in J2 to
=SUMPRODUCT([Carbs]*([Date]=[@Date]))
or better, to
=SUMIF([Date],[@Date],[Carbs])
and fill down.