Adding Summation Function to Vlookup Formula

LWest · Aug 6th, 2002, 07:35 PM

I posted this question before, however, the response was not exactly what I needed. As I still haven't figured out a solution, I will try to post the question again, and hopefully I can explain what I am trying to do a little better.

I am trying to add the summation function to a formula that has the vlookup function within it.

Here is an example:

Unit 1 Unit 2 Unit 3 Unit 4
income 5 4 3 2

I am trying to add the income for each of the 4 units using the vlookup function against income. So, my formula currently is as follows:

=vlookup("income",file,inc,2,false)+vlookup("income",file,inc,3,false)+vlookup("income",file,inc,4,false)+vlookup("income",file,inc,5,false)

In my live file; however, excel limits how many of these you can have and I receive a message stating my formula is too long. I thought there would be a way to add the sum function to this, but I cannot seem to get it correct. Any help would be appreciated. Thanks!

Aladin Akyurek · Aug 6th, 2002, 07:43 PM

Quote:

On 2002-08-06 13:35, LWest wrote:
I posted this question before, however, the response was not exactly what I needed. As I still haven't figured out a solution, I will try to post the question again, and hopefully I can explain what I am trying to do a little better.

I am trying to add the summation function to a formula that has the vlookup function within it.

Here is an example:

Unit 1 Unit 2 Unit 3 Unit 4
income 5 4 3 2

I am trying to add the income for each of the 4 units using the vlookup function against income. So, my formula currently is as follows:

=vlookup("income",file,inc,2,false)+vlookup("income",file,inc,3,false)+vlookup("income",file,inc,4,false)+vlookup("income",file,inc,5,false)

In my live file; however, excel limits how many of these you can have and I receive a message stating my formula is too long. I thought there would be a way to add the sum function to this, but I cannot seem to get it correct. Any help would be appreciated. Thanks!

I don't understand that file,inc bit in the formula that you use...

I have the impression that you could use a formula like

=SUMPRODUCT((A2:A100="Income")*(B2:E100))

judging from the sample you provided.

LWest · Aug 6th, 2002, 08:07 PM

The file,inc was just an example in the vlookup formula. In my file, I reference an external file to pull my data. Maybe it makes more sense to show you the exact formula I am using. Here it is...

=VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,11,FALSE)
+VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,12,FALSE)
+VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,13,FALSE)
+VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,14,FALSE)

So, I am adding the data from position 11-14 from the file named "July 02 YTD STAT", with a range name of "PL". And, what I would prefer to do is use the summation function to add these positions rather than doing it one-by-one. Thank you.

Aladin Akyurek · Aug 6th, 2002, 08:23 PM

Quote:

On 2002-08-06 14:07, LWest wrote:
The file,inc was just an example in the vlookup formula. In my file, I reference an external file to pull my data. Maybe it makes more sense to show you the exact formula I am using. Here it is...

=VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,11,FALSE)
+VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,12,FALSE)
+VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,13,FALSE)
+VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,14,FALSE)

So, I am adding the data from position 11-14 from the file named "July 02 YTD STAT", with a range name of "PL". And, what I would prefer to do is use the summation function to add these positions rather than doing it one-by-one. Thank you.

What I'd like to know whether there is just one record in the target file for the lookup value. If so, the formula I proposed would simply work. Would be less expensive than summong up the results of a multitude of VLOOKUPs.

LWest · Aug 6th, 2002, 08:50 PM

The file that I am using the vlookup function against is quite large, and I am pulling a multitude of data from it. There are about 20 columns and 500+ rows of data. The file is a download from SAP, and the rows can change from month to month if an account is added. That is why I am using the vlookup function so that my links remain in tact.

Aladin Akyurek · Aug 6th, 2002, 11:54 PM

Quote:

On 2002-08-06 14:50, LWest wrote:
The file that I am using the vlookup function against is quite large, and I am pulling a multitude of data from it. There are about 20 columns and 500+ rows of data. The file is a download from SAP, and the rows can change from month to month if an account is added. That is why I am using the vlookup function so that my links remain in tact.

Would you try out that formula to see whether it works? Lets take up dynamic/changing aspect of your data as next step.

BigC · Aug 7th, 2002, 04:58 AM

Any chance of just adding a =Sum(A1:E1) [where the cell references are those columns & rows which contain the income values for each unit]formula to each row (ie. in a new column) which will add the income values for all units together - and then returning this single value with the VLOOKUP?

[ This Message was edited by: BigC on 2002-08-06 23:01 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Aug 6th, 2002, 07:35 PM	#1
LWest Join Date: Jul 2002 Location: Chicago, Illinois Posts: 5	I posted this question before, however, the response was not exactly what I needed. As I still haven't figured out a solution, I will try to post the question again, and hopefully I can explain what I am trying to do a little better. I am trying to add the summation function to a formula that has the vlookup function within it. Here is an example: Unit 1 Unit 2 Unit 3 Unit 4 income 5 4 3 2 I am trying to add the income for each of the 4 units using the vlookup function against income. So, my formula currently is as follows: =vlookup("income",file,inc,2,false)+vlookup("income",file,inc,3,false)+vlookup("income",file,inc,4,false)+vlookup("income",file,inc,5,false) In my live file; however, excel limits how many of these you can have and I receive a message stating my formula is too long. I thought there would be a way to add the sum function to this, but I cannot seem to get it correct. Any help would be appreciated. Thanks!

Aug 6th, 2002, 08:07 PM	#3
LWest Join Date: Jul 2002 Location: Chicago, Illinois Posts: 5	The file,inc was just an example in the vlookup formula. In my file, I reference an external file to pull my data. Maybe it makes more sense to show you the exact formula I am using. Here it is... =VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,11,FALSE) +VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,12,FALSE) +VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,13,FALSE) +VLOOKUP(lookup!$B11,'July 02 YTD STAT.xls'!PL,14,FALSE) So, I am adding the data from position 11-14 from the file named "July 02 YTD STAT", with a range name of "PL". And, what I would prefer to do is use the summation function to add these positions rather than doing it one-by-one. Thank you.

Aug 6th, 2002, 08:50 PM	#5
LWest Join Date: Jul 2002 Location: Chicago, Illinois Posts: 5	The file that I am using the vlookup function against is quite large, and I am pulling a multitude of data from it. There are about 20 columns and 500+ rows of data. The file is a download from SAP, and the rows can change from month to month if an account is added. That is why I am using the vlookup function so that my links remain in tact.

Aug 7th, 2002, 04:58 AM	#7
BigC Join Date: Aug 2002 Location: Perth, Western Australia Posts: 358	Any chance of just adding a =Sum(A1:E1) [where the cell references are those columns & rows which contain the income values for each unit]formula to each row (ie. in a new column) which will add the income values for all units together - and then returning this single value with the VLOOKUP? [ This Message was edited by: BigC on 2002-08-06 23:01 ]