Forum Discussion

wcstarks's avatar
wcstarks
Iron Contributor
Jan 30, 2022
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...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jan 30, 2022

    wcstarks 

    Change the formula in J2 to

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

    or better, to

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

    and fill down.

HansVogelaar's avatar
HansVogelaar
MVP
Jan 30, 2022

wcstarks 

Change the formula in J2 to

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

or better, to

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

and fill down.

wcstarks's avatar
wcstarks
Iron Contributor
Jan 30, 2022

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.

Resources