Let's say I have a bunch of urls like so:
| URL | views |
|---|---|
| blah/?color=green&size=l | 50 |
| blah/?color=green&size=s | 50 |
| blah/?color=red&size=l | 87 |
| blah/?color=green&size=m | 60 |
| blah/?color=yellow&size=l | 32 |
| blah/?color=green&size=m | 10 |
I want to make a chart/pivot table or anything possible that will group by text (here, by the value of the color parameter) and show the sum of this group.
| URL | views |
|---|---|
| green | 170 |
| red | 87 |
| yellow | 32 |
How can I do such a thing?
CodePudding user response：
If you have Excel 365 then you can use this formula
=LET(Urls,A2:A7, Views,B2:B7,
UniqueColors,UNIQUE(MID(Urls,FIND("=",Urls) 1,FIND("&",Urls)-FIND("=",Urls)-1)),
ViewsSum,SUMIF(Urls,"*=" & UniqueColors & "&*",Views),
CHOOSE({1,2},UniqueColors, ViewsSum))
The formula
- gets unique colors from the URLs between "=" and "&" --> UniqueColors
- sums the views per color --> ViewsSum
- returns the new matrix build from UniqueColors and ViewsSum
