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?