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?

  • 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? – Raystafarian Feb 28 '15 at 18:39
  • Is this a real table, with structured references? You can probably use the OFFSET function for the equality test. – Ron Rosenfeld Mar 1 '15 at 11:50
  • It is a real table as in an Excel table. I tried using OFFSET and it didn't work. – Fela Maslen Mar 1 '15 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 '15 at 21:14
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.