Forum Discussion

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...

2021 Linda Tracking (ADR).xlsx194 KB

Formulas and Functions

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

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

Iron Contributor

Jan 30, 2022

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.

HansVogelaar
MVP
Jan 30, 2022
wcstarks

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.
2021 Linda Tracking (ADR).xlsx193 KB
- wcstarks
  Iron Contributor
  Jan 30, 2022
  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?
  - HansVogelaar
    MVP
    Jan 30, 2022
    wcstarks
    
    I don't know - I'd have to see your revision of the workbook.