Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 79
Default VLookup multiple values - sum returned values into single cell

I have found several items close to what i need but nothing exact. I need to
perform a vlookup consisting of multiple values (names of cities) then return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: VLookup multiple values - sum returned values into single cell

Yes, it is possible to perform a VLOOKUP consisting of multiple values and then return the sum of that result into a single cell. Here are the steps you can follow:
  1. First, make sure that the data you want to lookup and sum is organized in a table. For example, you could have a table with two columns: one for the city names and one for the values you want to sum.
  2. Next, select the cell where you want to display the sum of the lookup values.
  3. In that cell, type the formula "=SUM(".
  4. Now, click on the cell where you want to perform the VLOOKUP and type ", " (comma and space).
  5. Click on the cell that contains the lookup value (city name) you want to use in the VLOOKUP and type ", " again.
  6. Repeat step 5 for each additional lookup value you want to include in the VLOOKUP.
  7. Next, type the range of cells that contains the table you want to lookup from. For example, if your table is in cells A1:B10, you would type "A1:B10".
  8. Type a comma and then the column number of the value you want to sum. For example, if the values you want to sum are in column B of your table, you would type "2".
  9. Close the formula with a closing parenthesis.
  10. Press Enter to calculate the sum of the lookup values.

Here's an example of what the formula might look like:

Code:
=SUM(VLOOKUP("City1",A1:B10,2,FALSE),"City2",A1:B10,2,FALSE),"City3",A1:B10,2,FALSE))
In this example, the formula is looking up the values for City1, City2, and City3 in the table in cells A1:B10 and summing the values in column B.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default VLookup multiple values - sum returned values into single cell

Take a look SUMIF function in Help menu


"se7098" wrote:

I have found several items close to what i need but nothing exact. I need to
perform a vlookup consisting of multiple values (names of cities) then return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 79
Default VLookup multiple values - sum returned values into single cell

thanks...but i'm seeing a return of 0 when total should be 6332; where am i
going wrong with my formula?

=SUMIF([PremTech.xls]Summary!$A$2:$B$117,AD8:AD16,[PremTech.xls]Summary!$B$2:$B$117)

"Teethless mama" wrote:

Take a look SUMIF function in Help menu


"se7098" wrote:

I have found several items close to what i need but nothing exact. I need to
perform a vlookup consisting of multiple values (names of cities) then return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default VLookup multiple values - sum returned values into single cell

If this is what you have:

...........A.............B
1..Pittsburgh......10
2. Baltimore.......0
3..Pittsburgh......20
4..Cleveland......0
5..Cincinnati......-5

And you want to sum values that correspond to Pittsburgh:

=SUMIF($A$1:$A$5,"Pittsburgh",$B$1:$B$5)

Better to use cells to hold the criteria:

D1 = Pittsburgh

=SUMIF($A$1:$A$5,D1,$B$1:$B$5)

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
I have found several items close to what i need but nothing exact. I need
to
perform a vlookup consisting of multiple values (names of cities) then
return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 79
Default VLookup multiple values - sum returned values into single cell

i have multiple values to lookup...i.e., alpharetta, austin, bakersfield,
carrollton...i want to search for all of those and return the sum into a
single cell

City Customers
Alpharetta 2394
Anaheim 665
Antioch 482
Austin 356
Bakersfield 794
Boca Raton 363
Brunswick 142
Buena Park 339
Canoga Park 984
Carrollton 540


"T. Valko" wrote:

If this is what you have:

...........A.............B
1..Pittsburgh......10
2. Baltimore.......0
3..Pittsburgh......20
4..Cleveland......0
5..Cincinnati......-5

And you want to sum values that correspond to Pittsburgh:

=SUMIF($A$1:$A$5,"Pittsburgh",$B$1:$B$5)

Better to use cells to hold the criteria:

D1 = Pittsburgh

=SUMIF($A$1:$A$5,D1,$B$1:$B$5)

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
I have found several items close to what i need but nothing exact. I need
to
perform a vlookup consisting of multiple values (names of cities) then
return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default VLookup multiple values - sum returned values into single cell

List your criteria city names in a range of cells:

D1 = alpharetta
D2 = austin
D3 = bakersfield
D4 = carrollton

Big list of city names = A1:A10
Values to sum = B1:B10

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,D1:D4,0))),B1:B10)


--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
i have multiple values to lookup...i.e., alpharetta, austin, bakersfield,
carrollton...i want to search for all of those and return the sum into a
single cell

City Customers
Alpharetta 2394
Anaheim 665
Antioch 482
Austin 356
Bakersfield 794
Boca Raton 363
Brunswick 142
Buena Park 339
Canoga Park 984
Carrollton 540


"T. Valko" wrote:

If this is what you have:

...........A.............B
1..Pittsburgh......10
2. Baltimore.......0
3..Pittsburgh......20
4..Cleveland......0
5..Cincinnati......-5

And you want to sum values that correspond to Pittsburgh:

=SUMIF($A$1:$A$5,"Pittsburgh",$B$1:$B$5)

Better to use cells to hold the criteria:

D1 = Pittsburgh

=SUMIF($A$1:$A$5,D1,$B$1:$B$5)

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
I have found several items close to what i need but nothing exact. I
need
to
perform a vlookup consisting of multiple values (names of cities) then
return
the sum of that result into a single cell. Is this possible? brand new
to
vlookup so dummy it down for me, please. :)






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default VLookup multiple values - sum returned values into single cell

Try this:

=SUM(SUMIF(A:A,{"alpharetta","austin","bakersfield ","carrollton"},B:B))


"se7098" wrote:

i have multiple values to lookup...i.e., alpharetta, austin, bakersfield,
carrollton...i want to search for all of those and return the sum into a
single cell

City Customers
Alpharetta 2394
Anaheim 665
Antioch 482
Austin 356
Bakersfield 794
Boca Raton 363
Brunswick 142
Buena Park 339
Canoga Park 984
Carrollton 540


"T. Valko" wrote:

If this is what you have:

...........A.............B
1..Pittsburgh......10
2. Baltimore.......0
3..Pittsburgh......20
4..Cleveland......0
5..Cincinnati......-5

And you want to sum values that correspond to Pittsburgh:

=SUMIF($A$1:$A$5,"Pittsburgh",$B$1:$B$5)

Better to use cells to hold the criteria:

D1 = Pittsburgh

=SUMIF($A$1:$A$5,D1,$B$1:$B$5)

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
I have found several items close to what i need but nothing exact. I need
to
perform a vlookup consisting of multiple values (names of cities) then
return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 79
Default VLookup multiple values - sum returned values into single cell

Perfect!!!!! Thank you SO SO SO much!


"Teethless mama" wrote:

Try this:

=SUM(SUMIF(A:A,{"alpharetta","austin","bakersfield ","carrollton"},B:B))


"se7098" wrote:

i have multiple values to lookup...i.e., alpharetta, austin, bakersfield,
carrollton...i want to search for all of those and return the sum into a
single cell

City Customers
Alpharetta 2394
Anaheim 665
Antioch 482
Austin 356
Bakersfield 794
Boca Raton 363
Brunswick 142
Buena Park 339
Canoga Park 984
Carrollton 540


"T. Valko" wrote:

If this is what you have:

...........A.............B
1..Pittsburgh......10
2. Baltimore.......0
3..Pittsburgh......20
4..Cleveland......0
5..Cincinnati......-5

And you want to sum values that correspond to Pittsburgh:

=SUMIF($A$1:$A$5,"Pittsburgh",$B$1:$B$5)

Better to use cells to hold the criteria:

D1 = Pittsburgh

=SUMIF($A$1:$A$5,D1,$B$1:$B$5)

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
I have found several items close to what i need but nothing exact. I need
to
perform a vlookup consisting of multiple values (names of cities) then
return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 79
Default VLookup multiple values - sum returned values into single cell

That worked too! Thank you so very much also! This is the best resource i
have ever found. Thanks to all!

"T. Valko" wrote:

List your criteria city names in a range of cells:

D1 = alpharetta
D2 = austin
D3 = bakersfield
D4 = carrollton

Big list of city names = A1:A10
Values to sum = B1:B10

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,D1:D4,0))),B1:B10)


--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
i have multiple values to lookup...i.e., alpharetta, austin, bakersfield,
carrollton...i want to search for all of those and return the sum into a
single cell

City Customers
Alpharetta 2394
Anaheim 665
Antioch 482
Austin 356
Bakersfield 794
Boca Raton 363
Brunswick 142
Buena Park 339
Canoga Park 984
Carrollton 540


"T. Valko" wrote:

If this is what you have:

...........A.............B
1..Pittsburgh......10
2. Baltimore.......0
3..Pittsburgh......20
4..Cleveland......0
5..Cincinnati......-5

And you want to sum values that correspond to Pittsburgh:

=SUMIF($A$1:$A$5,"Pittsburgh",$B$1:$B$5)

Better to use cells to hold the criteria:

D1 = Pittsburgh

=SUMIF($A$1:$A$5,D1,$B$1:$B$5)

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
I have found several items close to what i need but nothing exact. I
need
to
perform a vlookup consisting of multiple values (names of cities) then
return
the sum of that result into a single cell. Is this possible? brand new
to
vlookup so dummy it down for me, please. :)








  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default VLookup multiple values - sum returned values into single cell

You are Welcome!

"se7098" wrote:

Perfect!!!!! Thank you SO SO SO much!


"Teethless mama" wrote:

Try this:

=SUM(SUMIF(A:A,{"alpharetta","austin","bakersfield ","carrollton"},B:B))


"se7098" wrote:

i have multiple values to lookup...i.e., alpharetta, austin, bakersfield,
carrollton...i want to search for all of those and return the sum into a
single cell

City Customers
Alpharetta 2394
Anaheim 665
Antioch 482
Austin 356
Bakersfield 794
Boca Raton 363
Brunswick 142
Buena Park 339
Canoga Park 984
Carrollton 540


"T. Valko" wrote:

If this is what you have:

...........A.............B
1..Pittsburgh......10
2. Baltimore.......0
3..Pittsburgh......20
4..Cleveland......0
5..Cincinnati......-5

And you want to sum values that correspond to Pittsburgh:

=SUMIF($A$1:$A$5,"Pittsburgh",$B$1:$B$5)

Better to use cells to hold the criteria:

D1 = Pittsburgh

=SUMIF($A$1:$A$5,D1,$B$1:$B$5)

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
I have found several items close to what i need but nothing exact. I need
to
perform a vlookup consisting of multiple values (names of cities) then
return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default VLookup multiple values - sum returned values into single cell

Just a warning with =sumif()...

If the sending workbook is closed, then =sumif() won't work correctly.

You may want to consider using those =sumproduct() formulas to avoid that
possible problem.

se7098 wrote:

thanks...but i'm seeing a return of 0 when total should be 6332; where am i
going wrong with my formula?

=SUMIF([PremTech.xls]Summary!$A$2:$B$117,AD8:AD16,[PremTech.xls]Summary!$B$2:$B$117)

"Teethless mama" wrote:

Take a look SUMIF function in Help menu


"se7098" wrote:

I have found several items close to what i need but nothing exact. I need to
perform a vlookup consisting of multiple values (names of cities) then return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please. :)


--

Dave Peterson
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default VLookup multiple values - sum returned values into single cell

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
That worked too! Thank you so very much also! This is the best resource i
have ever found. Thanks to all!

"T. Valko" wrote:

List your criteria city names in a range of cells:

D1 = alpharetta
D2 = austin
D3 = bakersfield
D4 = carrollton

Big list of city names = A1:A10
Values to sum = B1:B10

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,D1:D4,0))),B1:B10)


--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
i have multiple values to lookup...i.e., alpharetta, austin,
bakersfield,
carrollton...i want to search for all of those and return the sum into
a
single cell

City Customers
Alpharetta 2394
Anaheim 665
Antioch 482
Austin 356
Bakersfield 794
Boca Raton 363
Brunswick 142
Buena Park 339
Canoga Park 984
Carrollton 540


"T. Valko" wrote:

If this is what you have:

...........A.............B
1..Pittsburgh......10
2. Baltimore.......0
3..Pittsburgh......20
4..Cleveland......0
5..Cincinnati......-5

And you want to sum values that correspond to Pittsburgh:

=SUMIF($A$1:$A$5,"Pittsburgh",$B$1:$B$5)

Better to use cells to hold the criteria:

D1 = Pittsburgh

=SUMIF($A$1:$A$5,D1,$B$1:$B$5)

--
Biff
Microsoft Excel MVP


"se7098" wrote in message
...
I have found several items close to what i need but nothing exact. I
need
to
perform a vlookup consisting of multiple values (names of cities)
then
return
the sum of that result into a single cell. Is this possible? brand
new
to
vlookup so dummy it down for me, please. :)








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple returned values into a single cell Jeff Excel Discussion (Misc queries) 12 May 29th 09 11:41 PM
Vlookup when multiple values can be returned [email protected] Excel Worksheet Functions 9 February 20th 08 08:43 PM
vlookup, multiple values, sum values into one cell?? Phillips L Excel Worksheet Functions 4 November 9th 05 01:31 AM
create a list of single values from multiple values Jordan Excel Worksheet Functions 3 November 4th 05 12:25 AM
Search multiple values to return single values JANA Excel Worksheet Functions 8 October 27th 05 04:26 PM


All times are GMT +1. The time now is 10:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"