1. ## Excel Formula (2002/SP3)

Hi,

In the example that I've attached, I'm trying to figure out a formula that would give me the sum of the last X numbers starting from the bottom of the list. Can I do this using an Excel formula, or do I need to use VBA?

2. ## Re: Excel Formula (2002/SP3)

Assuming your table always ends in row 26:
=SUM(OFFSET(C26,0,0,-G6,1))

3. ## Re: Excel Formula (2002/SP3)

If the row is variable you can use:
=SUM(OFFSET(INDIRECT("C"&MATCH(E3+1000,C:C,1)),0,0 ,-G6,1))

4. ## Re: Excel Formula (2002/SP3)

Thank you Mike.

Two questions:
1) What does MATCH(E3+1000,C:C,1) accomplish? ie, what is E3 for?
2) Can you explain to me what does the INDIRECT function do in this case?

5. ## Re: Excel Formula (2002/SP3)

<hr>What does MATCH(E3+1000,C:C,1) accomplish? ie, what is E3 for?<hr>
That, unfortunately is a double typo. It should have been MATCH(3E+100,C:C,1)

=SUM(OFFSET(INDIRECT("C"&MATCH(3E+100,C:C,1)),0,0,-G6,1)) broken down:

Match(3E+100,C:C,1) returns the last row with a number in it.
indirect("C"&MATCH(3E+100,C:C,1)) sets the beginning cell for the OFFSET function.
OFFSET(INDIRECT("C"&MATCH(3E+100,C:C,1)),0,0,-G6,1) creates a range starting in the last row in the C column that is as tall as the value in G6.
Sum() adds the range created using the Offset function.

6. ## Re: Excel Formula (2002/SP3)

Thanks for the great help. :-)

7. ## Re: Excel Formula (2002/SP3)

Or try……

=SUM(OFFSET(C1,MATCH(9.99E+307,C:C)-1,0,-G6))

Regards
Bosco

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•