=SUM formula changing when I delete rows

[mickyd67]

The experts will take a nanosecond to answer this, I'm sure.

=sum(A1:A100)

If I then delete 50 rows my formula changes to =SUM(A1:A50)

How do I stop that happening? (don't say "don't delete any rows" )

 

[Joe4]

Try:

=SUM(INDIRECT("A1:A100"))

 

[Fluff]

How about

=SUM(A1:INDEX(A:A,100))

 

[mickyd67]

Thanks to you both (I've been offline for a bit) - the first answer worked for me, I didn't try the 2nd answer yet.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[mickyd67]

Try:

=SUM(INDIRECT("A1:A100"))

Hello - I have another question on this one.

What if I wanted to use this formula in one tab, but calculate a sum from another tab?

i.e.:

=SUM(INDIRECT("A1:A100")) - but I want that formula to calculate data in a tab called: "Dashboard & Power Query Data"

I've tried a few things but I keep getting an error.

Anyone got any idea?

Thanks.

 

[Joe4]

=SUM(INDIRECT("A1:A100")) - but I want that formula to calculate data in a tab called: "Dashboard & Power Query Data"

Try:

=SUM(INDIRECT("'Dashboard & Power Query Data'!A1:A100"))

(note the use of single quotes around the sheet name due to the spaces in the name).

 

[mickyd67]

Try:

=SUM(INDIRECT("'Dashboard & Power Query Data'!A1:A100"))

(note the use of single quotes around the sheet name due to the spaces in the name).

Thank you again - I think it was the single quote that was catching me out.

 

[mickyd67]

OK - hopefully one last question....

If I want to use COUNTIF and reference another page I get an error message.

For instance

=COUNTIF('02) Safety Level Status'!I5:I1500,">0")

But I want to put INDIRECT in there - the last part ,">0") seems to be catching me out.

Any suggestions?

 

[Joe4]

But I want to put INDIRECT in there - the last part ,">0") seems to be catching me out.

You only put INDIRECT around the range reference, not the whole formula.
So the format would be:
=COUNTIF(INDIRECT(range reference),">0")