How to group similar rows in Excel, and total the values of each group?

Question

I have a spreadsheet with similar items in different rows. Each item has a unique value. For example:

I want to create a table that has one row for each of the similar rows (e.g., /ca/motion-type/abstract-4), and a sum of the values in the second column (e.g., 30).

Any idea how I can do this in Excel?

Answer 1

In D2, formula copied down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$9)/ISERR(FIND("?",A$2:A$9))/ISERR(FIND("/",A$2:A$9,17)),ROW(A1))),"")

In E2 formula copied down :

=IF(D2="","",SUMIF(A:A,D2&"*",B:B))

Answer 2

Here's one solution, based on a few assumptions:

1. All pages start with /ca/motion-type/
2. There's always at least one row containing the "base" url, e.g. ...abstract-4
3. After removing /ca/motion-type/, any url not containing a ? or / character should be considered a base url

Leave me a comment if any of these assumptions aren't correct and you're unable to adapt the solution. In that case I can try to adapt the solution for you.

Solution

Formulas used:

Formulas for easy copy/pasting:

D2: =MID(A2;17;9999)

E2: =AND(ISERROR(FIND("?";D2));ISERROR(FIND("/";D2)))

A14: =FILTER(A2:A9;E2:E9)

B14: =SUMIFS($B$2:$B$9;$A$2:$A$9;"="&A14&"*")