How to replicate SUMIF, AVERAGEIF and COUNTIF functions in Looker Studio?

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.


Need help ? We offer Looker Studio Support for companies.

Looking for a connector ? Check Supermetrics.


SUMIF & AVERAGEIF, just a CASE WHEN with 0s or nulls

In this 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.

looker studio sumif averageif countif

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 !


Previous article: How to send a weekly report by email in Looker Studio?

Next article: How to build good looking gauges in Looker Studio?

Looking for something else? All our articles can be found on our blog page