AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
1.7K members online now
1.7K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Countif in Metric

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I am attempting to use a Gsheet to power quarterly reporting and would like to formulate a metric using "countif." The value is being able to report a count of Yes/No in a certain column that can be chosen dynamically based upon the date range filter used at the time. If I do a countif formula on the sheet itself, it would only provide the full static count of the column, not shifting with filter.

 

Is there another way to successfully create that type of a metric that I'm missing here?

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Mario A
May 2017

Countif in Metric

Visitor ✭ ✭ ✭
# 16
Visitor ✭ ✭ ✭

Success! I created a new metric with the following formula and it worked!

 

SUM(CASE WHEN Dimension = "Yes" THEN 1 ELSE 0 END)

 

Big thanks to Litmon and John W. for trying to assist me with my issue.

View solution in original post

Countif in Metric

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆

The way I've gotten around this is to create a column in Google Sheets where I include the value "1" in every row. Then, in DS, I set up that value as a sum, and then when it's filtered, it's essentially a count of how many rows meet that value.

Countif in Metric

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Will the Date range filter in DS impact the Google Sheet? My goal is go get the Number of "Yes" cells in a column for a given range selected in DS.

Countif in Metric

Participant ✭ ✭ ☆
# 4
Participant ✭ ✭ ☆

Do you have multiple rows with the same date?

Countif in Metric

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Yes. I will have up to 500 rows with the same date. 

Countif in Metric

Participant ✭ ✭ ☆
# 6
Participant ✭ ✭ ☆

Ok, how many different dependencies does the countif have? For instance, are you just trying to count how many dates there are? Or will you also want to filter by other things too (like channel or device type or whatever)?

Countif in Metric

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

For simplicity, Google sheet has:

Column A: Date

Column B: Yes/No

----

Data Studio displays

DS: Date filter 

DS: Scorecard

----

Now if I choose dates ranging from 5/1/17 - 5/12/17, I would like to see the scorecard display a total of "Yes" values in column B for the given dates. 

Countif in Metric

Rising Star
# 8
Rising Star

Sounds like you could use the query function in Google Sheets to solve this. Something like, =query(A:A, " select count (A) where A matches '$date' label count (A) ' ' "). Where A is a list of dates. The query function is pretty powerful and, despite only a few commands (e.g. count, sum, max etc) will allow you to do SQL like queries. There's a few ways to introduce a dynamic date in Google Sheets but a good start is the setting which allows them to recalculate date automatically. In my example, you can dynamic populate $date based on another cells contents, which can be a date. 

 

 

John Wedderburn, Advertiser Community, Rising Star
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.

Countif in Metric

Participant ✭ ✭ ☆
# 9
Participant ✭ ✭ ☆

If you're looking to work just within the Google Sheets environment, it sounds like John's solution is your best bet. 

 

If you do need to filter it to DS, then here's a formula workaround so that you get a unique count of dates (but if anyone has a better way, please chime in, this is the convoluted method I've been using that I'd love to improve):

 

1. Make sure your GS data is in date order

2. Create a new column that will be a metric to count the number of unique dates

3. Assume your date is in A2, in the new unique date field type =if(A2=A1,0,1)

 

This assesses whether that date has already been counted. If it has, then it creates a value of 0 so it doesn't count it again. Otherwise, it creates a value of 1 so it gets counted.

Countif in Metric

Visitor ✭ ✭ ✭
# 10
Visitor ✭ ✭ ✭

Thanks John, but I'm trying to get the result through dynamic date ranges in DS instead of in Sheets. The report I've created spans multiple data sources to report on trends across different tasks and hopping through sheets would be a cumbersome way of trying to produce the result I need. I truly do appreciate the information though, I'm not a user that's familiar with SQL although it is something I've been anxious to dive into.