How to group certain data in a chart in Google Sheets

Question

I'm creating a chart of a number of items (website traffic sources) and want to find a way to merge any rows that contain 'Facebook' in the chart.

Here's the raw data:

(direct)            67
192.com             0
bing                21
en-gb.facebook.com  0
facebook.com        2
google              429
l.facebook.com      10
l.instagram.com     0
linkedin.com        0
lm.facebook.com     18

And here's how I'd like the data to look before I create the chart:

(direct)        67
192.com         0
bing            21
Facebook        30
google          429
l.instagram.com 0
linkedin.com    0

I'm aware I could do this using column filters but the data is updated automatically each day so filtering won't work.

I think what I need is a more dynamic solution (maybe a query?) but not sure how to go about writing this.

Any clever people out there know how I could achieve this?

Answer 1

You can try to use sumif:

=SUMIF(range,"*text*",sum_range)

in your case using different criteria on each row for summarizing based on one word:

=SUMIF(range,"*facebook*",sum_range)

=SUMIF(range,"*instagram*",sum_range)

etc.