Sum n results from VLOOKUP

[gauntletxg]

I currently have this VLOOKUP function:

=VLOOKUP($E$3,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))

I am using it to look up inventory numbers from an external workbook, based on supplier ID and month. This works great.

The problem is sometimes multiple suppliers are rolled up into one. So to get a sum for those instances, I currently modify the formula to something like this:

=VLOOKUP($E$3,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))+VLOOKUP($E$4,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))

Basically just repeating the formula and changing the ID I need to look up. But there has to be an easier way to do this. This method gets lengthy, inefficient, and is a pain to debug. Ideally I'd like to select a range of say 5 cells that each contain a unique ID, and tell the formula to look all of them up and sum the results.

How would I go about doing this?

Thanks.

 

[gauntletxg]

Well, this works

=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX(INDIRECT(R:R),,1),$E$3:$P$3,0))),INDEX(INDIRECT(R:R),,3))

Where R:R contains my workbook name and named range. Now I just need to see if I can replace this with DSUM or something....these calcs are slooooooow.

Suggestions?

 

[turtle44]

I have this same problem, but don't have gauntletxg's command of excel formulas and am not sure how to get this to work despite the fact he's already posted an answer to his own question.

Basically, I have 256 different detailed customer segment rows (each having their own unique ID) which contain several columns of data. I am trying to sum together certain of these detailed customer segments to arrive at higher level segments.

Example: I have one larger segment "Large Customer Segment" for which I need to sum together data from 25 different customer segment rows. For the sake of argument/ease, let's say every segment has a value of 100 in each column.

Text strings for each segment I need to lookup in the database:
"Customer Segment 2" (1st segment row to be summed)
"Customer Segment 6" (2nd segment row to be summed)
"Customer Segment 33" (3rd segment row to be summed)
"Customer Segment 45" (4th segment row to be summed)
"Customer Segment 56" (5th segment row to be summed)
"Customer Segment 109" (6th segment row to be summed)
......
"Customer Segment 243" (25th segment row to be summed)

Database:
Column A B C
Row 1 Customer Segment 1 100 100
Row 2 Customer Segment 2 100 100
Row 3 Customer Segment 3 100 100
...
Row 256 Customer Segment 256 100 100

I need a formula that can take the 25 different text strings I have, find the exact match in column A and add together each value in column B to arrive at a total of 2500 (each value in column B is 100 in this example). As I mentioned, each customer segment in the database is unique, if that makes a difference.

I don't need to validate that column B has numbers or do anything else like that. I'm certain that the segment text string I'm looking up is in the database and that there's only one occurrence of it. It would work fine to simply sum up VLOOKUP statements for each text string, but Excel will not let me do that for the number of text strings I have. I'd rather have one formula in one cell if at all possible. I'd prefer not to use VBA/macros, as I'm not that experienced in them and neither would anyone who might inherit my work.

Thanks. I love this board and often spend an afternoon cruising around learning things.

 

[gauntletxg]

You don't have quite the same issue I was having...you want to sum based on multiple unique values, I was summing based on one. See if this helps you:

Excel Workbook
	A	B	C	D
1	ID	Value	Criteria	Formula
2	1	10	ID	150
3	2	20	2
4	3	30	5
5	4	40	8
6	5	50
7	6	60
8	7	70
9	8	80
10	9	90
11	10	100
Sheet1

A:B is your database, where A is your customer segment and B is the value you want to sum. C is your criteria, so in each successive cell you'd place the segments you want to rollup. The 2 in the second argument of DSUM refers to the column you want to sum (relative to the database).

 

[sailepaty]

If I got what you are trying to do, this might works.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Range of data:A2:C256<o></o>
Range of list of customer to add:E2:E26<o></o>
<o> </o>
=SUMPRODUCT(SUMIF(A2:A256,E2:E26,C2:C256))<o></o>
<o> </o>
Regards<o></o>

 

[turtle44]

Thank you both so much. I tried both methods and they work and are much easier to read/debug than summing VLOOKUP statements. Even though Sailepaty's method was marginally more complicated (one more formula) which might slow down my spreadsheet, I ended up choosing it because it fit the structure of my data better - I didn't have headings (i.e., "ID") atop each group of customer segments that I'm trying to sum up. Sailepaty's method could just call out data ranges without headers, which worked better for me at this point.

This is a big timesaver and I'm sure the person that inherits this work will appreciate it even more than me. Thanks again.