Use VLOOKUP in conjunction with a sum formula

[DH888]

I have a spreadsheet that calculates investment returns continuing to calculate growth after retirement up to 100 years of age. (Not unthinkable for the youth of this day and age)

Start with this so you can see what I'm trying to do.
https://drive.google.com/open?id=0B01KC2Iq-_wKNjVCbm82N0JleWc

3rd sheet (Early Retirement) (Current sheet as you open it (Saved State)) Oops I guess saved state doesn't take online only if you download it.
What I would like to happen in cell M71 is I would like it to sum column M up to 100 years old (Column J)
I'm assuming I could use a vlookup for this but I'm not sure the syntax to use to use both a sum statement and a vlookup in the same formula.

I tried this =SUM(M10:VLOOKUP((100),$J$10:$N70,)
but that's basically all I've got. It doesn't work anyway because what I actually need to do is then take the number from column I (the prior column) and multiply it by "-$M$7" (The annual withdrawal) but as far as I know a lookup has to start with the column you are looking up and can only get values from columns to the right of that. Is there a way I can do the lookup on column J, get the value from column I and multiply it by cell "$M$7"

No that won't work either because that assumes the values in column M are all the same which works if the person's age and starting capital allow the investment to draw the full annual income "M7" to 100 years of age but you can see in the next table that is not always going to be the case so I'm back to a sum formula. I need the lookup to get the 100 from column J and then do a sum of column M up to and including the "Age 100 row" This would solve the problem of including the partial annual withdrawal. The reason I need to do it like this is because there's actually a bunch of places I would like to implement this functionality once it's perfected and I can't just sum the column up to 100 because the 100th year is going to change from rows depending on the age that activates this table. Change cell J9 to 65 to see what I mean. Then CTRL+Z to change it back. It's all linked to other functionality.

Thanks in advance for your help

 

[BGY23]

Hi, I can't download your file, security here won't permit it. But you're probably looking for a Sumif(

 

[RoryA]

Something like this should do it:

=SUM(M10:INDEX($M$10:$M$70,MATCH(100,$J$10:$J$70,0)))

 

[DH888]

Man Rory that was FAST and works like an absolute charm. You're amazing thanks so much.

 

[DH888]

Rory can you tell me what the first M10 does. I've started adapting your formula for a bunch of places I want to use it in this workbook and it worked great for column L and N but when I tried to use it on Column S I get #N/A. This is what I tried to use.
=SUM(S10:INDEX($S$10:$S$70,MATCH(100,$Q$10:$U$70,0)))

It didn't work for column T either. not sure what I'm doing wrong
=SUM(T10:INDEX($T$10:$T$70,MATCH(100,$Q$10:$U$70,0)))

 

[RoryA]

MATCH, like VLOOKUP, only works to look up data in one column so the blue bit here:
MATCH(100,$Q$10:$U$70,0)
can only refer to one column.

 

[DH888]

BGY23 I'm not sure what you mean I didn't upload it to mrexcel I uploaded it to my google drive. Many people from this site have been able to download it from my google drive. Just use the link I provided in my initial post.

 

[DH888]

Oh I see what I did. Thanks for the prompt response and the useful information.

I would still like to know what the first M10 is for though
=SUM(M10:INDEX($M$10:$M$70,MATCH(100,$J$10:$J$70,0)))

 

[DH888]

Hey Rory I could really use the answer to my query

what is the first M10 for
=SUM(M10:INDEX($M$10:$M$70,MATCH(100,$J$10:$J$70,0)))

I'm itching to use this formula in a bunch of places in the workbook I'm working on but don't want to just duplicate this formula until I know I need all components especially the first M10 in this example because I've been googling sum and index together and NONE of them are structured this way. They are all missing that first M10 placeholder. Does this mean I don't need it. Is it unnecessary. I'm sure it's not since you used it but if it's to define the number that will be summed based on the first example I gave you using the parameters I gave you that works in that first situation but will not be appropriate for others who are summing completely different values.

 

[RoryA]

M10 is just the cell the sum range starts from.