Vlookup query

[seagull over nz]

Hi, I'm new to this Board so apologies if this is going over old topics, I had a quick search and couldn't find a thread that matched my query. I want to adapt a lookup function to make it a bit more simple. Take for instance a series of numbers like this:

1 10
2 15
3 21
4 25
5 13
6 12

Now in my look up formula I would like to sum a range. For instance in the first cell I would like to sum the value of the range 3 to 5 with the answer as 59 (21+25+13). How do I create a vlookup formula that pulls in the values corresponding to a certain range? Does that make sense. At the moment I have a long string of vlookups for 3, 4 and 5 within the same cell formula but it becomes a bit tiresome.

Thanks in advance.

 

[Sixthsense]

Just explain us how you are fixing these ranges (21+25+13)?

 

[Jonmo1]

Welcome to the board

Which version of Excel are you using?
This will work in XL 2007+

This sums B1:B100 if A1:A100 between 3 and 5 (C1=3, D1=5)
=SUMIFS(B1:B100,A1:A100,">="&C1,A1:A100,"<="&D1)

 

[Jugantarji]

Assuming your data is in A1:A6 and you were putting your top and bottom values in d1 and d2

=SUM(INDIRECT(ADDRESS(ROW(Sheet1!A1:A6)+MATCH(D1,A1:A6,FALSE)-1,COLUMN(A1:A6)+1)):INDIRECT(ADDRESS(ROW(A1:A6)+MATCH(D2,Sheet1!A1:A6,FALSE)-1,COLUMN(A1:A6)+1)))

 

[Useful]

In this sample your range A1:A6. insert your values to "B1" as from, "C1" as to

=SUM(INDIRECT("A"&B1):INDIRECT("A"&C1))

 

[seagull over nz]

Just explain us how you are fixing these ranges (21+25+13)?

So my example is an accounting scenario. The numbers 1 to 5 represent account codes and the numbers in the 2nd column represent the values of £'s spent against those account codes so say for instance account codes 3 to 5 represented staff costs on a separate tab I want to show "Staff Costs" being the sum of account codes 3 to 5. I am using Excel 2010 if that helps.

 

[seagull over nz]

Welcome to the board

Which version of Excel are you using?
This will work in XL 2007+

This sums B1:B100 if A1:A100 between 3 and 5 (C1=3, D1=5)
=SUMIFS(B1:B100,A1:A100,">="&C1,A1:A100,"<="&D1)

Thanks for this. My only issue here is that the table of data that it is summing is a pivot table and the numbers That are in "column A" are stored as text rather than numbers. Is there a way of making the formula treat text as numbers?

 

[Useful]

it converts text to numbers "--"
--(A1:A6)

 

[Jonmo1]

Thanks for this. My only issue here is that the table of data that it is summing is a pivot table and the numbers That are in "column A" are stored as text rather than numbers. Is there a way of making the formula treat text as numbers?

I would urge you to go to the source data of the pivot table and make then real numbers, instead of "numbers stored as text"
That will be more helpful to you in the long run.


But you can try this instead on Text #s in col A

=SUMPRODUCT(--(A1:A100+0>=C1),--(A1:A100+0<=D1),B1:B100)

 

[seagull over nz]

I would urge you to go to the source data of the pivot table and make then real numbers, instead of "numbers stored as text"
That will be more helpful to you in the long run.


But you can try this instead on Text #s in col A

=SUMPRODUCT(--(A1:A100+0>=C1),--(A1:A100+0<=D1),B1:B100)

That's great, works a treat.

Out of interest as it could be helpful in the future, how do I change the pivot source data to numbers rather than text. I can't find an option within the pivot table options.

Can't believe I haven't found this forum before, could be very useful. Hope I can help others as well!!