1

I have a table with columns named "date", "cost" and "daily". "Date" is a date, "cost" is a number and "daily" is a formula calculating the total cost of each day. The formula looks like this:

=IF(A2=A3,"",SUMIF(A$2:A2,A2,B$2:B$2)

This checks that the current row's date is different from the next row's, and if so, returns the sum of costs of the rows with the current date.

The problem occurs when I add new data to the table. Suppose there are 10 rows. Then the cell C10 will have the formula:

=IF(A10=A11,"",SUMIF(A$2:A10,A10,B$2:B$2)

But if I add a new row (e.g. by pressing tab while the cell C10 is selected), then C10's formula is automatically changed to:

=IF(A10=A12,"",SUMIF(A$2:A11,A10,B$2:B$2)

Furthermore, the style of the cell is reset so it is no longer bold text, and I get a warning next to the cell indicating "this cell is inconsistent with the column formula".

How do I prevent this behaviour?

4
  • You're inserting a row at C10 which shifts the current C10 to C11. If you insert at C11, this won't happen. Are these things correct? Feb 28, 2015 at 18:39
  • Is this a real table, with structured references? You can probably use the OFFSET function for the equality test. Mar 1, 2015 at 11:50
  • It is a real table as in an Excel table. I tried using OFFSET and it didn't work. Mar 1, 2015 at 17:13
  • 1. What about the middle A10? Doesn't it also change to A11? 2. How do you insert rows with Tab key? When I press Tab, the selected cell is changed, but no row is inserted.
    – ZygD
    Mar 10, 2015 at 21:14

1 Answer 1

0

You can use offset() instead. ( :

So you formula becomes :

=IF(A2=offset(A2,1,0),"",SUMIF(A$2:offset(A2,0,0),A2,B$2:B$2)

In my test dataset it works, where adding rows doesn't change the formula by removing the dependencies.. please share if it doesn't.

p/s : Things (cell reference change) happened this way because the formula you defined are 'depending' on the A11 values. And the action of 'insert rows' will 'retain' the original cell referenced in C10's formula. Which had been 'moved' to A12 due to the row insertion.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .