Lookup then sum formula

[lisa duncan]

I need help with a formula that will lookup several different criteria then sum the result from a differnet column for example below are two columns I need to find all of the criteria in column A (cities and states) and sum column B : I have 260 cities and states to look through but need to sum only six.


<TABLE style="WIDTH: 177pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=235><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 101pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2717586 class=xl173 height=20 width=134>Abbeville LA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl179 width=101 align=right>601874.50</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl173 height=21>Aberdeen WA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl179 align=right>564900.86</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl173 height=21>Albany KY</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl179 align=right>278053.41</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl173 height=20>Alexandria LA HH</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl179 align=right>635333.75</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl173 height=20>Amarillo TX</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl179 align=right>1049488.92</TD></TR></TBODY></TABLE>

 

[sheetspread]

Just use sumif

 

[braindiesel]

Are you familiar with SUMIF

=sumif(Range of city names,city you want to find, range of numbers you want to add up)

So, with two columns, A & B holding cities and Amounts respectively
I would put the following in cell E2, with cell D2 having the name of the city you want

=sumif($A$2:$A$261,D2,$B$2:$B$261)

 

[sheetspread]

Excel Workbook
	A	B	C
1	Abbeville LA	601874.5
2	Aberdeen WA	564900.86
3	Albany KY	278053.41
4	Alexandria LA HH	635333.75
5	Amarillo TX	1049488.92
6	Abbeville LA	47662
7	Aberdeen WA	302476
8	Abbeville LA	472343
9	Aberdeen WA	542717
10	Abbeville LA	468074
11	Abbeville LA	71019
12	Aberdeen WA	39536
13	Albany KY	422879
14	Alexandria LA HH	460291
15	Amarillo TX	927289
16
17
18
19
20		Abbeville LA	1660973
Sheet3

 

[lisa duncan]

The problem is I want to find six different cities in a listing of 260 and only sum the six.

 

[T. Valko]

The problem is I want to find six different cities in a listing of 260 and only sum the six.

Do you want 6 formulas to sum for the 6 criteria or do you want 1 sum for all 6 criteria combined?

 

[lisa duncan]

I need one sum for all six criterias combined

 

[T. Valko]

I need one sum for all six criterias combined

Ok, try this...

List the criteria in a range of cells, say D2:D7.

Book1
	A	B	C	D
2	7	83	_	1
3	4	73	_	2
4	15	73	_	3
5	4	42	_	4
6	13	24	_	5
7	9	66	_	6
8	8	60	_	_
9	3	8	_	421
10	11	23	_	_
11	6	45	_	_
12	6	49	_	_
13	11	39	_	_
14	3	65	_	_
15	10	36	_	_
16	9	95	_	_
17	2	79	_	_
18	12	66	_	_
19	11	37	_	_
20	6	60	_	_
Sheet1

Then, this formula entered in D9:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,D2:D7,0))),B2:B20)

 

[lisa duncan]

Great It works!! Thank you so much

 

[T. Valko]

Great It works!! Thank you so much

You're welcome. Thanks for the feedback!