Totalling lists

P

Peter

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.
 
D

Domenic

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!
 
A

Anne Troy

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
 
D

Domenic

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!
 
A

Anne Troy

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
 
D

Domenic

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

Cheers!
 
Top