| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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. |
|
#7
|
|||
|
|||
|
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. |
|
#8
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using/referencing custom lists | Excel Worksheet Functions | |||
| loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
| Comparing lists of map references | Excel Worksheet Functions | |||
| Dropdown lists | Excel Discussion (Misc queries) | |||
| Comparing 2 Customer Lists to Identify Shared Customers | Excel Worksheet Functions | |||
Linear Mode
