VLookup multiple values - sum returned values into single cell

[se7098]

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.

 

[Teethless mama]

Take a look SUMIF function in Help menu

 

[se7098]

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)

 

[T. Valko]

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)

 

[se7098]

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]

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,D14,0))),B1:B10)

 

[Teethless mama]

Try this:

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

 

[se7098]

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

Try this:

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

 

[se7098]

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

 

[Teethless mama]

You are Welcome!

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

 

[Dave Peterson]

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.

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)

Take a look SUMIF function in Help menu

 

[T. Valko]

You're welcome. Thanks for the feedback!