Best way to sum totals for URLs with and without trailing slash

VLOOKUP returns data, it does not sum.

Perhaps you can use something like this:

=SUM(SUMIF(A:A,{"company.com/blog","company.com/blog/"}))

hi RF

I'd suggest to add a column to replicate the page name and force a trailing / or remove, either way to make the lines be the same then use the sumif or a pivot tabl.

so eg, column c formula =if(right(a1,1)="/",a1,a1&"/")
this will put in a trailing slash if it's not there.

Ron

You should also be able to use this to sum them up:
=SUMIFS(B2:B5,A2:A5,"/blog/blogA*")

Thanks all for your replies. I think the issue with Gregb11 and 63falcondude's solutions is that they don't work neatly (as I can see) with the way I get the data. There are several hundred rows, and because the URLs with the trailing slash are the "main" pages, these will all cluster at the top. I basically just want to "top up" these rows with the pageviews of their counterpart URLs without the slash, which all cluster at the bottom of the dataset.

Greg's solution returns nothing for the first rows (because it's looking for variations like /blog/blogA//), and I can't figure out how to get falcondude63's solution to dynamically update to the rows.

In the end I think rondeondon's idea works best. Thinking it through, I ended up with this forumula instead:

=SUM(B2,(IFERROR(VLOOKUP((LEFT(A2,LEN(A2)-1)),A:B,2,FALSE),0)))

It uses VLOOKUP to find the pageviews for the URL without the trailing slash. This was then returning #N/A if it didn't find it, hence I added the IFERROR formula to add 0 if the slash-less URL wasn't found.

So turns out something like this:

URL	Pageviews	Combined Pageviews
/blog/blogA/	179	184
/blog/blogB/	126	131
/blog/blogC/	125	134
/blog/blogD/	124	139
/blog/blogE/	86	95
/blog/blogD	15	15
/blog/blogC	9	9
/blog/blogE	9	9
/blog/blogA	5	5

I usually only report on the top 20 pages and there are hundreds, so as long as I make sure I'm reporting on the rows that have a slash (which include pageviews with and without slash), my reporting will be accurate.