Sum values between two vlookup cells?

[j13]

Hi -

I'm trying to create a sheet where a user can enter a number in one cell, then a number in another cell, then have the spreadsheet sum the values between those two numbers in a vlookup.

Specifically, I have a spreadsheet with population breakdowns for every year of age (i.e. age 1 has 3,000, age 2 has 4,000, age 5 has 6,000, etc.).:

A B
0 2000
1 3000
2 4000
3 5000
4 6000
[...]
100 25000

I've named the array that includes those ages and the corresponding population numbers "AGESHEET".

In another sheet, I have cell where a lower number is entered, and a higher number is entered:

A B
[Lower Age] [Higher Age]

What I want is for column C to return the sum of numbers between those two ages. So, for instance, if someone wanted the population for people between 2 and 4, it should look up those values in column A and sum everything in between them in column B (in this case, 4,000+5,000+6,000=15,000):


A B C
[Lower Age=2] [Higher Age=4] [Sum of populations2-4 in array AGESHEET=15,000]

I can set up a VLoookup that returns the populations of the upper and lower boundaries (i.e. one that returns 4,000 or 6,000), but I can't figure out how to get it to sum all of the cells between those values.

Any help would be very much appreciated!

Thanks.

[arthurbr]

Hi and welcome to the board

you can try something like

=SUMPRODUCT(((SHEET1!A1:A100)>=lower_age)*((sheet1!a1:a100<=higher_age))*B1:B100)

using 2007 you can also use SUMIFS

I can't give more help, don't have XL under Linux

More on SUMPRODUCT http://www.xldynamic.com/source/xld....T.html#classic

[Andrew-R]

I created two seperate ranges, one called "Age" and one called "Population" and then went with...

Please Login or Register  to view this content.

Where the lower age was in cell D3 and the upper age was in cell E3.

So it takes the total of all ages and subtracts the sum of the lower ages plus the sum of the higher ages.

Tested and seems to work.

[j13]

Hi and welcome to the board

you can try something like

=SUMPRODUCT(((SHEET1!A1:A100)>=lower_age)*((sheet1!a1:a100<=higher_age))*B1:B100)

using 2007 you can also use SUMIFS

I can't give more help, don't have XL under Linux

More on SUMPRODUCT http://www.xldynamic.com/source/xld....T.html#classic

Thanks so much for your help (your's also, Arthur-R). It turned out that "sumifs" was the way to go - I used a function that summed if the values were above the lower threshold and below the upper threshold and it worked well.

Thanks again!