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

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.

• Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. You can even upvote the answer. If you don't have enough reputation, your upvote will show up later, once you reach enough reputation points. Apr 29 at 19:02

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.

• This works! How can I replicate this with another 5 columns of data (I only used two here for illustration)?
– manc
Apr 28 at 21:22
• Can you clarify what you mean by 'another 5 columns' in relation to the above? Apr 28 at 23:09
• One column above is sessions, but have another 5 or so columns (ie bounce rate, average time per session, etc)
– manc
Apr 29 at 7:39

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) '' ")
``````

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.