Formula error after deleting rows

[dougmcc1]

I have this formula:
=IF(C1=35,SUM(D4:D38)+D41+D42+D43,SUM(D4:D31)+D34+D35+D36)

But when I delete rows 32-38 it changes to this formula:
=IF(C1=35,SUM(D4:D31)+D34+D35+D36,SUM(D4:D31)+#REF!+#REF!+#REF!)

And that results in an output value of #REF!

How do I make it so that when I delete those 7 rows, the formula calculates the sum of the shortened range?

[Jonmo1]

The IDEAL solution is to NOT delete those rows.
Could you instead just "Clear Contents" of those rows?

[dougmcc1]

It's a spreadsheet I use at work and people would wonder why there's a bunch of blank rows.

I was hoping there'd be a way I can adjust the formula so that it would ignore the rows that got deleted when C1 becomes 28 instead of 35.

[GregTheSquarePeg]

You could adjust the first occurance of formula manually, lock any references you don't want to change ($) then drag the new formula over the area again.

or

You could just hide the rows you don't want, instead of deleting them.

[FDibbins]

you have this...
=IF(C1=35,SUM(D4:D38)+D41+D42+D43,SUM(D4:D31)+D34+D35+D36)
and then you delete the rows that the bolded part references (plus a few extra 32:38 either side), so excel is kinda saying...+ ummm errr wait, those references dont exist any more, tell me what to do???

Im surprised the 1st part didnt throw out an error too...SUM(D4:D38)

Probably the vbest thing would be to either delete the #REF references (use find/replace) or just hide those empty rows?

[martindwilson]

you could try
=IF(C1=35,SUM(INDIRECT("D4:D36"),INDIRECT("D41:D42")),SUM(INDIRECT("D4:D31"),INDIRECT("D34:D36")))

[dougmcc1]

you could try
=IF(C1=35,SUM(INDIRECT("D4:D36"),INDIRECT("D41:D42")),SUM(INDIRECT("D4:D31"),INDIRECT("D34:D36")))

Beautiful, Martin!