Nesting Formulas

[MartinLiss]

I have this simple formula
=SUM(F3:F8)

and I'd like to replace the 8 with this.
=COUNT(F1:F9999)-1

How do I do that?

[Pepe Le Mokko]

Hi and welcome
try=SUM(F3:INDEX(F3:F9999,count(F3:F9999)-1))

[MartinLiss]

Thanks......

[Richard Buttrey]

..another option woud be to create a dynamic range name, e.g. 'mysum', defined as

Please Login or Register  to view this content.

The the SUM formula is

Please Login or Register  to view this content.

[MartinLiss]

Thanks but that's very similar to the method I first tried and it gives a circular reference error because in my case the formula is in column F and F:F encompasses the whole column.

[Richard Buttrey]

Thanks but that's very similar to the method I first tried and it gives a circular reference error because in my case the formula is in column F and F:F encompasses the whole column.

That of course will be the case if the =mysum is in column F
For various reasons it's not good practice to use whole columns.

The other option then is to ensure the SUM formula is above your data. e.g assumin your data starts at F10 with the mysun range defined as say

Please Login or Register  to view this content.

an

Please Login or Register  to view this content.

anywhere in F1:g9 will avoid a circular reference