Vlookup in a range and summarize each total

[david_cc]

Hello guys,

Can someone help me with this case?

I want to sum all impressions per item in the LOOKUP list. The Input Name often contain others words which can be before or after the city name. Also there can be a difference in capital letter and small letter for city names. Please see the example:

INPUT	Impressions		LOOKUP	OUTPUT
keyword 1 + Paris	2
keyword 2 + Paris	1		Paris	10
keyword 3 + Paris	3		Bordeaux	17
keyword 4 + Paris	4		Marseile	15
keyword 1 + Bordeaux	3
keyword 2 + Bordeaux	3
keyword 3 + Bordeaux	5
keyword 4 + Bordeaux	6
keyword 1 + Marseile	3
keyword 2 + Marseile	4
keyword 3 + Marseile	5
keyword 4 + Marseile	3

<tbody>
</tbody>

Looking forward to some replies

best!

 

[63falcondude]

Try

Excel 2010
	A	B	C	D	E
1	INPUT	Impressions		LOOKUP	OUTPUT
2	keyword 1 + Paris	2
3	keyword 2 + Paris	1		Paris	10
4	keyword 3 + Paris	3		Bordeaux	17
5	keyword 4 + Paris	4		Marseile	15
6	keyword 1 + Bordeaux	3
7	keyword 2 + Bordeaux	3
8	keyword 3 + Bordeaux	5
9	keyword 4 + Bordeaux	6
10	keyword 1 + Marseile	3
11	keyword 2 + Marseile	4
12	keyword 3 + Marseile	5
13	keyword 4 + Marseile	3
Sheet4

Cell Formulas
Range	Formula
E3	=SUMIF(A:A,"*"&D3&"*",B:B)

 

[david_cc]

Try

Excel 2010

	A	B	C	D	E
1	INPUT	Impressions		LOOKUP	OUTPUT
2	keyword 1 + Paris	2
3	keyword 2 + Paris	1		Paris	10
4	keyword 3 + Paris	3		Bordeaux	17
5	keyword 4 + Paris	4		Marseile	15
6	keyword 1 + Bordeaux	3
7	keyword 2 + Bordeaux	3
8	keyword 3 + Bordeaux	5
9	keyword 4 + Bordeaux	6
10	keyword 1 + Marseile	3
11	keyword 2 + Marseile	4
12	keyword 3 + Marseile	5
13	keyword 4 + Marseile	3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula E3 =SUMIF(A:A,"*"&D3&"*",B:B) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

[/Nice Thanks a lot! Now I have the case that some city names look quite the same. For example:

Saint Malo
Saint Malon sur mel
Saint malo en donziois

How can I change the formula to only SUM the total of every seperate city?

Thanks in advance!

 

[63falcondude]

Nice Thanks a lot! Now I have the case that some city names look quite the same. For example:

Saint Malo
Saint Malon sur mel
Saint malo en donziois

How can I change the formula to only SUM the total of every seperate city?

Thanks in advance!

As far as I know, in this application, there is no way to tell "blahblah Saint Malo blahblah" apart from "blahblah Saint Malo sur mel blahblah" in one formula.