CODEWITHSUNDEEP
excelexcel-formulasum
MattTheDoorMat
MattTheDoorMat

Reputation: 3

Excel, SUM across all sheets not including last sheet when adding new row

I have a simple SUM for every corresponding cell from all 12 sheets (12 months) ranging from April 2023-March 2024

This works fine, but when I add a new line (a new customer) it only adds the SUM of 11 sheets (months), not all 12.

My formula is:

=SUM('10th April 2023:10th March 2024'!E15)

When I add a new line (to add a new customer) it changes to this:

=SUM('10th April 2023:10th February 2024'!E16)

The end of the formula for some reason won't stay as my final sheet name "10th March 2024" but my 2nd to last, "10th February 2024". This happens no matter how many new lines I add too.

I've dragged the same formula across the entire row, yet when I add a new table line the formula for the new lines changes to months April-February rather than April-March

Upvotes: 0

Views: 163

Answers (1)

MattTheDoorMat
MattTheDoorMat

Reputation: 3

I've finally worked out the answer to my own question!

I had to "clear contents" on my table and re-write the formula and copy it down.

Every time I added a new line to the table it was using the old formula and wasn't updating to the new one.

Clearing the contents of the table fixed this.

Upvotes: 0

Related Questions