# Vlookup query

#### seagull over nz

##### New Member
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.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Just explain us how you are fixing these ranges (21+25+13)?

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)

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

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

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

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.

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?

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

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)

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!!

Replies
3
Views
229
Replies
1
Views
241
Replies
9
Views
296
Replies
26
Views
775
Replies
10
Views
239

1,203,756
Messages
6,057,162
Members
444,909
Latest member
Shambles111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back