Perform sum even after deleting some rows from a sum range

[skonduru]

Hi,

I have a multiple range of data which is not continuous and performed sum of the multiple range. Now, i would like to delete some rows which are not needed anymore but it throws an #REF! error. Is there a way around it? by which i mean, can i perform sum without throwing errors even after some rows within multiple range are deleted

Thanks,

Kon.

[skonduru]

Can anyone help me with this please?

[Ochimus]

skonduru,

Haven't tested this, but If you "name" your range of non-continuous cells, this might work?.

=IF(ISNUMBER(your formula here),your formula here,"")

Or alternatively:

=SUMIF(yourrange,">=0")+SUMIF(yourrange,"<0")

Either of those should ignore all cells with errors, text and the like.

Ochimus

[Tony Valko]

Post your formula.

[skonduru]

Thanks Ochimus and Tony..

Currently, i am just using

=SUM(K45:K51)+SUM(K54:K60)+SUM(K63:K81)+SUM(K84:K87)+SUM(K90:K93).

I would like user to delete rows 83 to 94 which will throw #REF! error. Even after deleting i would like to perform addition of remaining cells without giving error.

[Tony Valko]

Try it like this...

=SUM(K45:K51,K54:K60,K63:K81,INDIRECT("K84:K87"),INDIRECT("K90:K93"))

[skonduru]

Thanks Tony.

But if i use indirect function, it will make those references constant, instead i would to have those references as dynamic.

[Tony Valko]

i would to have those references as dynamic.

Not sure what you mean by that.

If you delete the rows then what should happen to those ranges in the formula?

=SUM(K45:K51,K54:K60,K63:K81,K84:K87,K90:K93)

[skonduru]

The highlighted ranges will become #REF! s, is there a way to force sum to ignore those errors?

[Tony Valko]

The only way I know how to do it is by using INDIRECT.

[skonduru]

Ok. Thanks Tony.

Can anyone else help me with this issue?

[Tony Valko]

Let me post this in our "Need help with this" section.

[MrShorty]

What is in the "in between" cells (K52 and K53 for example) that makes it undesirable to use =SUM(K45:K93)? With a single range in your sum function, Excel will update the range without the range-ref errors when you delete or insert rows. As you have it, you get these errors whenever you delete all of the rows associated with one of the SUM() functions. Any solutions I see to this involve getting rid of the "piecemeal" summation and make a single "sum" range. Exactly how I would do that depends on what is in those "in between" rows.

[Tony Valko]

The only way I know how to do it is by using INDIRECT.

It could also be done with OFFSET but the net result is the same.

After the rows are deleted the data is deleted but the deleted ranges are still evaluated ("active").

[skonduru]

Thanks for your input MrShorty.

I have sub totals in between K52 and K53 which i dont want to include when performing grand total of all subtotals.

[MrShorty]

Is it necessary for the subtotals to be in column K? Could they be moved over to column L? If the subtotals are in their own column, then the grand total can either be the sum of column K or the sum of column L?

Is it necessary to exclude the subtotal cells from the grand total? Including the subtotals in the grand total would mean "doubling" the result (since each value effectively gets counted twice). Dividing by 2 should correct for the double inclusion. =sum(K45:K93)/2

Is there some indicator in another column that could be used in a =SUMIF() or SUMIFS() function to pick out the values to include in the grand total? If something like "subtotal" is in column J where the subtotals are, then you could use =SUMIF(J45:J93,"subtotal",K45:K93).

I'm sure there are a lot of different options. Like I indicated above, all the options that I would put forth involve some strategy for getting rid of the "piecemeal" way you are currently defining the sum range and make the sum range a single block of cells.

[skonduru]

Hi MrShorty, SumIF strategy seems like a way to go.

Appreciate your help alot.

Kon

[Pete_UK]

Going back to the formula you posted in Post #5, i.e.:

=SUM(K45:K51)+SUM(K54:K60)+SUM(K63:K81)+SUM(K84:K87)+SUM(K90:K93)

you could change this to:

=IFERROR(SUM(K45:K51),0)+IFERROR(SUM(K54:K60),0)+IFERROR(SUM(K63:K81),0)+IFERROR(SUM(K84:K87),0)+IFERROR(SUM(K90:K93),0)

and if you then deleted rows 83 to 94 that would then change to:

=IFERROR(SUM(K45:K51),0)+IFERROR(SUM(K54:K60),0)+IFERROR(SUM(K63:K81),0)+IFERROR(SUM(#REF!),0)+IFERROR(SUM(#REF!),0)

but it would still return a result of the sum of the first 3 ranges.

Hope this helps.

Pete