2

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.

1

2 Answers 2

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.

4
  • 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
  • I've added additional explanation to the answer showing how to add additional columns to the query result. Apr 29 at 9:48
0

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.

1
  • Since -after your request you altered your question, I took the liberty of changing the title to: Sum of all numbers from many columns that match text from multiple rows in another column You can change it back if you disagree. Apr 29 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.