Sum of all numbers from many columns that match text from multiple rows in another column

Question

I have some data from analytics that I need to combine somehow and trying to find the best way of doing this. I have data similar to the following:

URL	Sessions
/	59
/?variable=1	2
/?variable=2	37
/?variable=3	1
/another-page/	21
/another-page/?variable=x	1

I want to be able to find a way to first remove any UTM data (ie anything after the question mark) then add up the total sessions from each page. Here, the result I want is this:

URL	Sessions
/	99
/another-page/	22

I want this formula to be automatic in the way that it read all URLs in Column A until it reaches the end.

Answer 1

If your source table is in columnns A & B of a sheet, you can use the following formula in any cell in any other column:

=arrayformula(query({{A1;regexreplace(A2:A,"\?.+",)},B:B},
"select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2) 'Sessions'",1))

The REGEXREPLACE finds a '?' with any number of characters following it and replaces the whole lot with null (i.e. it removes them). Then QUERY summarises by the result.

EDITTo sum more columns, reference them in the input array and then add additional 'sum(ColX)'s to the select clause, e.g for four columns to be summed:

=arrayformula(query({{A1;regexreplace(A2:A,"\?.+",)},B:E},
"select Col1,sum(Col2),sum(Col3),sum(Col4),sum(Col5) where Col1 is not null group by Col1",1))

Override the default column labelling of required using the label clause as shown in the first answer.

Answer 2

Another (easier to manage) way of doing this is:

=QUERY({INDEX(REGEXREPLACE(A2:A,"\?.+","")),
        BYROW(B2:D,LAMBDA(ss,SUM(ss)))},
            "select Col1, sum(Col2) where Col1<>'' 
             group by Col1 label sum(Col2) '' ")

(Do adjust the formula according to your ranges and locale)

If you want to extend to more columns you just change the range from B2:D (3 columns) to e.g. B2:P (15 columns)

In this formula we use BYROW to first sum all columns by row to just one column, Col2.
This way we avoid the repeating pattern sum(Col2),sum(Col3),sum(Col4),sum(Col5), sum(Col6),sum(Col7),sum(Col8),sum(Col9)...... etc.