vlookup and sumproduct combo?

[rfinnegan]

Hi All:

I’m trying to perform a vlookup on a file in which the figures I’m looking for are set as text and have accomplished that with the help of this board [ =VLOOKUP(TEXT($A7,"0"),'data.xls'!$A$1:$CB$99,59,0) ].

Now I’m trying to show the sum of 4 rows of data in the data.xls sheet, all of which have to the same vlookup value.

For example – I’m performing a vlookup on a file named data.xls. The only identifier I can use in my vlookup is the store number (e.g. 14) which is in column A. In addition to the file being formatted as text, the store number appears from 5-8 times in column A, once for each product type. I need a formula that will find the store number, and then look in column G which has the product number (1-6), and then sum the figures in column L for products 3,4,5,6.

In other words what I’m trying to tell Excel is - Go to the data.xls file and look for the number 14 in column A (even though it’s formatted as text),
If column A=14, and column G=3, take the number in column L
If column A=14, and column G=4, take the number in column L
If column A=14, and column G=5, take the number in column L
If column A=14, and column G=6, take the number in column L
Now add all the column L figures from above.

I’m trying to do this in Excel 2003 .

Thanks in advance.

 

[Aladin Akyurek]

Hi All:

I’m trying to perform a vlookup on a file in which the figures I’m looking for are set as text and have accomplished that with the help of this board [ =VLOOKUP(TEXT($A7,"0"),'data.xls'!$A$1:$CB$99,59,0) ].

Now I’m trying to show the sum of 4 rows of data in the data.xls sheet, all of which have to the same vlookup value.

For example – I’m performing a vlookup on a file named data.xls. The only identifier I can use in my vlookup is the store number (e.g. 14) which is in column A. In addition to the file being formatted as text, the store number appears from 5-8 times in column A, once for each product type. I need a formula that will find the store number, and then look in column G which has the product number (1-6), and then sum the figures in column L for products 3,4,5,6.

In other words what I’m trying to tell Excel is - Go to the data.xls file and look for the number 14 in column A (even though it’s formatted as text),
If column A=14, and column G=3, take the number in column L
If column A=14, and column G=4, take the number in column L
If column A=14, and column G=5, take the number in column L
If column A=14, and column G=6, take the number in column L
Now add all the column L figures from above.

I’m trying to do this in Excel 2003 .

Thanks in advance.

Is it not simply...

=SUMPRODUCT(--($A$2:$A$100+0=14),--ISNUMBER(MATCH($G$2:$G$100,{3,4,5,6},0)),$L$2:$L$100)

where you can use placeholders for 14 and for the set of 3, 4, 5, and 6?