CODEWITHSUNDEEP
excel
Vincent Decaux
Vincent Decaux

Reputation: 10714

Excel - Group by duplicate and SUM or AVERAGE

I have this sheet (data from Google Analytics) :

 A        |B      |C        |D
 URI      |Views  |ViewsUn. |Time
 -----------------------------------------------------
 /uri-1   |123    |100      |10
 /uri-2   |200    |180      |5.2
 /uri-1   |50     |35       |5

I want to group by URI, and add the number of views and unique views, and make an average on time, I want this output :

 A        |B      |C        |D
 URI      |Views  |ViewsUn. |Time
 -----------------------------------------------------
 /uri-1   |173    |135      |7.5
 /uri-2   |200    |180      |5.2

I am trying with SUMPRODRUCT but it looks like I have to already have all my uri-1 without duplicate, and then loop this new array.

Is there a way to do it faster ? Or through the interface / functionalities ?

Upvotes: 0

Views: 325

Answers (1)

SIE_Vict0ria
SIE_Vict0ria

Reputation: 194

There are multiple ways to do this. One would be a Pivot Table; the other would be to essentially create your own pivot table with formulas. I personally prefer the latter because Pivot Tables do not automatically refresh themselves as data is added to the source and it's not easy to tell just by looking at a Pivot Table if it has been refreshed or not.

To create a Pivot Table...

Select the data, and in the Insert tab, select pivot table:

enter image description here

Set your pivot table fields as shown below:

enter image description here

To do the same thing with formulas so the content is always up to date...

If you're using Microsoft 365, you can use the UNIQUE function. Let's say your data is in columns A through D. Input the following functions:

Cell G1 =UNIQUE(A:A)
Cell G1 ="URI"
Cell G2 =IF($F2="","",SUMIF($A:$A,$F2,$B:$B))     [Drag this down]
Cell H1 ="Views"
Cell H2 =IF($F2="","",SUMIF($A:$A,$F2,$C:$C))     [Drag this down]
Cell I1 ="ViewsUn"
Cell I2 =IF($F2="","",AVERAGEIF($A:$A,$F2,$B:$B)) [Drag this down]
Cell J1 ="Time"
Cell J2 =IF($G2="","",AVERAGEIF($A:$A,$G2,$B:$B)) [Drag this down]

In the strategy above, the table will stay live or current as you continue to add additional URIs, as long as you have the formulas dragged down to cover all the possible URI rows that might get populated, but the IF statement makes sure they are blank when no URI is detected.

If you're not using Microsoft 365, you won't have access to the UNIQUE function. In this case there are several other ways to get unique lists. In your case I would probably do the following:

Cell F1 ="Index"
Cell F2 =1
Cell F3 =$F2+1           [Drag this down]
Cell G1 ="URI"
Cell G2 =" /uri-"&F2     [Drag this down]
Columns H:J as above

Upvotes: 1

Related Questions