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.
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
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 !
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
Communicate and browse privately. Check Proton Mail and Proton VPN
Website hosted by Tropical Server