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. :)

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:

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.

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. :)

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. :)

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. :)

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. :)

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. :)

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. :)

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. :)

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. :)

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. :)

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

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. :)