Deleted rows in spreadsheet cause incorrect SUM

[jazzy]

I have got a spreadsheet in which I’ve deleted some rows (they’re not hidden).
Specifically rows 35, 36, & 37 have been deleted,

so the rows are numbered {1,2,3,…34, 38,39,40…}
I want to calculate the SUM total of a column like SUM(I1:I47) but this give me incorrect summation.

Workaround is to do something like this:

=SUM(I1:I34) + SUM(I38:I47)

At first I didn’t even notice the deleted rows and couldn’t figure out why summation was incorrect. Eventually I noticed that the row numbers weren’t sequential and there was a missing gap. I thought perhaps they were “hidden” but trying to “show” hidden rows doesn’t unhide any rows. The rows have been deleted.

I’d really like all the rows to be numbered sequentially, and if I insert a new row or delete the numbering of the rows should be adjusted to keep the numbering sequential with no missing gaps (or if there are gaps, then the SUM formula should be smart enough to realize that those rows don’t exist and to not include the missing rows in the summation).

[erAck]

@jazzy

SUM(I1:I47) but this give me incorrect summation

Incorrect how? What is the data, what is the result, and what would expect instead?

@jazzy

Specifically rows 35, 36, & 37 have been deleted
[…]
Workaround is to do something like this:
=SUM(I1:I34) + SUM(I38:I47)

Given that you said you deleted rows 35,36,37 that makes non sense. Apparently you did not delete the rows but replaced their content with some other numbers. Even if it were empty cells then SUM() ignoring those would not change the result.

Best upload a sample file.

[jazzy]

=SUM(I30:I34)+SUM(I38:I47) => $192,681.32 (correct)

=SUM(I30:I47) => $303,352.29 (wrong)

[jazzy]

Please note that there is a gap in the sequential numbering of the rows which causes SUM() to give incorrect summation. Not sure why deleting rows resulted in gap with missing sequential numbers or whether this behavior is normal, but obviously it results in wrong result.

[jazzy]

Just noticed that I can insert rows before Row #47 (Row #47 gets pushed down to Row #48 and new blank Row #47 is created).
But I cannot similarly insert rows below Row #34 (Nothinig happens). I would have expected a blank Row #35 to be created after Row #34.

[jazzy]

OK, I fixed the problem in the spreadsheet. Something was apparently wrong with Rows #34 & #38. So I deleted both rows and then the rows got re-numbered correctly in sequence again and SUM() worked.

[erAck]

@jazzy

Please note that there is a gap in the sequential numbering of the rows

You did not delete the rows, you set them to 0 height or hid them (though you said they’re not hidden, they are).

[LeroyG]

@jazzy

Something was apparently wrong with Rows #34 & #38

Maybe it was the Group function (the left border of your screenshot suggest that). Try this in a new spreadsheet:

• Select rows 35:37
• Press F12 (or choose menu Data - Group and Outline - Group)

To Ungroup, press Ctrl+F12.

[PKG]

@jazzy
Maybe you just used the wrong function?

[jazzy]

OK, you were right. The rows were hidden. Figured out you just can’t right-click and select “Unhide” but you have to first select the shown rows backeting the hidden rows before “Unhiding”.

It works a little differently than hide/unhide for sheets, where if you select unhide for sheets your given a list of hidden sheets to pick from when unhiding.