Sum variants of same cell - Attention Web Analysts

[doug firr]

Any ideas on a smart way to do the following are appreciated. I'm a web analyst and have a sheet with a list of pages.

On this list there are variants of the same page - www and non www, .asp , /index etc etc.

If there were only 10 or so pages I could just manually sum the matching pages but there are hundreds of pages, each with a variant www VS non www etc.

Any ideas on how to approach?

cheers
Paul

 

[Robert Mika]

Hi Paul,
Could you give an example?

 

[doug firr]

Hi Robert

To be more specific I have a spreadsheet with a list of URLs and the number of visits to each URL. There are hundreds of urls but some of them are variants of the same page.

The home page, for example, appears on numerous rows due to the variants:

www.companyname.com
companyname.com
www.companyname.com/index
companyname.com/index
http://www.companyname.com
etc
etc

These are all the same page, I believe the term is a "cononical issue".

So thats for one page. There are also variants for example of the products page:

www.companyname.com/products
companyname.com/products

So there is a column with all these page names and in the column next to it there are the number of visits to each page.

For the example above to get the correct number of visits to the products page I'd have to add the visits for both the www and non www variants of the page.

It'd be easy if there was just ten or so pages but there are hundreds of pages each with variants.

Any ideas? Do I have to bite the bullit and write up a long formula?

 

[T. Valko]

Hi Robert

To be more specific I have a spreadsheet with a list of URLs and the number of visits to each URL. There are hundreds of urls but some of them are variants of the same page.

The home page, for example, appears on numerous rows due to the variants:

www.companyname.com
companyname.com
www.companyname.com/index
companyname.com/index
http://www.companyname.com
etc
etc

These are all the same page, I believe the term is a "cononical issue".

So thats for one page. There are also variants for example of the products page:

www.companyname.com/products
companyname.com/products

So there is a column with all these page names and in the column next to it there are the number of visits to each page.

For the example above to get the correct number of visits to the products page I'd have to add the visits for both the www and non www variants of the page.

It'd be easy if there was just ten or so pages but there are hundreds of pages each with variants.

Any ideas? Do I have to bite the bullit and write up a long formula?

Try something like this...

Book1
	A	B
1	www.companyname.com	64
2	companyname.com	18
3	www.companyname.com/index	11
4	companyname.com/index	90
5	http://www.companyname.com	67
6
7
8
9	Sum for:
10	companyname	250
11
12	Formula entered in B10:
13	=SUMIF(A1:A5,"*"&A10&"*",B1:B5)
Sheet1

 

[Robert Mika]

And to extract the company name use:

=MID(A2,FIND(".",A2)+1, FIND("@",SUBSTITUTE(A2,".","@",2))-FIND(".",A2)-1)

 

[Peter_SSs]

And to extract the company name use:

=MID(A2,FIND(".",A2)+1, FIND("@",SUBSTITUTE(A2,".","@",2))-FIND(".",A2)-1)

If you check you will find that does not work on all the samples as quoted in post #3 and shown in the screen shot in post #4.

 

[Robert Mika]

Hi Peter,
You are absoluelu right and thank you for pointing this out.

Here is my solution:

=IF(ISNUMBER(FIND("www",A1)),MID(A1,FIND(".",A1)+1, FIND("@",SUBSTITUTE(A1,".","@",2))-FIND(".",A1)-1),LEFT(A1,FIND(".",A1)-1))