How to replicate SUMIF, AVERAGEIF and COUNTIF functions in Looker Studio?
You’re the king of SUMIF, AVERAGEIF and COUNTIF in Excel, but can’t make them work in Looker Studio? No worries, we’ve got it covered for…
Most people around us playing with Looker Studio come with a solid background in Excel / Google Sheet. Thing is, functions don’t have the same name between tools and that can be very frustating at time. So, in this short article, we’ll show you how to replicate SUMIF, AVERAGEIF and COUNTIF functions in Looker Studio.
Looker Studio connectors we use on a daily basis and are happy with (w/ free trials): Supermetrics - Windsor - Catchr - PMA - Funnel
Not sure which one to pick? Reviews here and there. Still unsure?! Contact us with your tech stack and budget, and we’ll guide you in the right direction:
SUMIF & AVERAGEIF, just a CASE WHEN with 0s or nulls
In an older article, we wrote everything you needed to about CASE WHEN.
Well, almost everything.
Here, we’re going one step beyond into the world of calculated fields. So, let’s say you’ve got stocks data, and want to create 2 columns to split your investments between Google and Apple. You could take advantage of a good old SUMIF to isolate, but it doesn’t work this way in Looker Studio.
Instead, use a CASE WHEN statement to replicate SUMIF. In your table: Add metric, create field, and then paste the formula below:
CASE
WHEN Stock=’GOOG’ THEN Invested
ELSE 0
END
And that’s pretty much it ! What we’ve done here is to tell Looker Studio to replace values with 0 when the condition is not met, so it doesn’t sum. We kept the auto aggregation to SUM, but we could also have enclosed the formula with a SUM(…) to force it.
Very similar for AVERAGEIF: just change the auto aggregation and you’re almost ok? NO, BEWARE: if you are to to do an AVERAGEIF, replace 0 with null. You don’t want to average ( valueA valueB 0 valueC 0) / 5; You just want to average ( valueA valueB valueC) /3, and to do so use nulls instead of 0s.
If you get this trick, SUMIF and AVERAGEIF will have no secret for you.
COUNTIF, same CASE WHEN but with 1s
For COUNTIF, same philosophy, with one caveat. Instead of suming values, we’re going to sum 1s, which means that your formula now looks like this:
CASE
WHEN Stock=’GOOG’ THEN 1
ELSE 0
END
Again, we kept the auto aggregation to SUM, or we could also have enclosed the formula with a SUM(…), and it would work like a charm. You’re very welcome.
In this short article, we’ve reviewed how to replicate SUMIF, AVERAGEIF and COUNTIF functions in Looker Studio
PROBLEM SOLVED !
Tools we use and we like:
Todoist: for tasks management
Monday: for projects management, when tasks fall short
Tropical Server: our friends in charge of the hosting of this very website!
Tools we’d like to use (if we had the budget…):