Prevent Excel from changing formula of last row of table when adding a new row

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

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