Totalling lists
 

I want to add a total to lists of variable lengths when I don't know how long
the list will be. I want to do this by formula and not using the subtotal
function. Any ideas? Thanks.
--
Peter
London, UK

Assuming that Column A contains your list and the first row contains
your label, try...

=SUM(A2:INDEX(A:A,MATCH(9.9999999999999E+307,A:A)) )

Hope this helps!

In article ,
"Peter" wrote:

I want to add a total to lists of variable lengths when I don't know how long
the list will be. I want to do this by formula and not using the subtotal
function. Any ideas? Thanks.

Hi, Peter. Same Peter? :)
What I usually do in this case is put my headings in Row 2 and my totals in
Row 1, where the formula in Row 1 might be something like =Sum(A3:A65536).
************
Anne Troy
www.OfficeArticles.com

"Peter" wrote in message
...
I want to add a total to lists of variable lengths when I don't know how
long
the list will be. I want to do this by formula and not using the subtotal
function. Any ideas? Thanks.
--
Peter
London, UK

Thanks both!
--
Peter
London, UK


"Peter" wrote:

I want to add a total to lists of variable lengths when I don't know how long
the list will be. I want to do this by formula and not using the subtotal
function. Any ideas? Thanks.
--
Peter
London, UK

Dominic: I'd like to add that to my articles. Can you describe it? I'd LOVE
that. :)
************
Anne Troy
www.OfficeArticles.com

"Domenic" wrote in message
...
Assuming that Column A contains your list and the first row contains
your label, try...

=SUM(A2:INDEX(A:A,MATCH(9.9999999999999E+307,A:A)) )

Hope this helps!

In article ,
"Peter" wrote:

I want to add a total to lists of variable lengths when I don't know how
long
the list will be. I want to do this by formula and not using the subtotal
function. Any ideas? Thanks.

Hi Anne! It would be my pleasure... :)

As you can see, since the 'Match Type' for the MATCH function is
omitted, it defaults to 1.

As such, the MATCH function searches for the largest value that is less
than or equal to the lookup value and returns its relative position.

Here, the lookup value is 9.9999999999999E+307, which is the highest
number that Excel recognizes. It's a number that won't likely occur in
the range. So MATCH finds the last numerical value in the range and
returns its position.

This number is then used as an argument for the INDEX function which in
turn returns a reference....

=SUM(A2:<Reference Returned by INDEX/MATCH)

Hope this helps!

In article ,
"Anne Troy" wrote:

Dominic: I'd like to add that to my articles. Can you describe it? I'd LOVE
that. :)
************
Anne Troy
www.OfficeArticles.com

"Domenic" wrote in message
...
Assuming that Column A contains your list and the first row contains
your label, try...

=SUM(A2:INDEX(A:A,MATCH(9.9999999999999E+307,A:A)) )

Hope this helps!

In article ,
"Peter" wrote:

I want to add a total to lists of variable lengths when I don't know how
long
the list will be. I want to do this by formula and not using the subtotal
function. Any ideas? Thanks.

Terrific. I like to give credit where credit is due. Can I use your name? If
so, provide how you'd like it to appear. I'm happy to provide a link to your
web if you have one. :)
************
Anne Troy
www.OfficeArticles.com

"Domenic" wrote in message
...
Hi Anne! It would be my pleasure... :)

As you can see, since the 'Match Type' for the MATCH function is
omitted, it defaults to 1.

As such, the MATCH function searches for the largest value that is less
than or equal to the lookup value and returns its relative position.

Here, the lookup value is 9.9999999999999E+307, which is the highest
number that Excel recognizes. It's a number that won't likely occur in
the range. So MATCH finds the last numerical value in the range and
returns its position.

This number is then used as an argument for the INDEX function which in
turn returns a reference....

=SUM(A2:<Reference Returned by INDEX/MATCH)

Hope this helps!

In article ,
"Anne Troy" wrote:

Dominic: I'd like to add that to my articles. Can you describe it? I'd
LOVE
that. :)
************
Anne Troy
www.OfficeArticles.com

"Domenic" wrote in message
...
Assuming that Column A contains your list and the first row contains
your label, try...

=SUM(A2:INDEX(A:A,MATCH(9.9999999999999E+307,A:A)) )

Hope this helps!

In article ,
"Peter" wrote:

I want to add a total to lists of variable lengths when I don't know
how
long
the list will be. I want to do this by formula and not using the
subtotal
function. Any ideas? Thanks.

Actually, I don't have a website. This is purely a hobby for me. But
thank you very much for your consideration.

Cheers!

In article ,
"Anne Troy" wrote:

Terrific. I like to give credit where credit is due. Can I use your name? If
so, provide how you'd like it to appear. I'm happy to provide a link to your
web if you have one. :)
************
Anne Troy
www.OfficeArticles.com