COUNTIFS Custom Calculation Function in Looker?

I’m trying to replicate a COUNTIFS function in Looker, and cannot figure it out for the life of me. The COUNTIFS works in Sheets but I’m not familiar enough with Looker functions to translate it so hoping someone can help. 

The COUNTIFS is simply =COUNTIFS(criteria_range-1,criterion-1,criteria-range-2,criterion-2). The criterion themselves are cell references in this instance, as the result I am looking for is a count of how many lines of data match these two criterion within the data set. The actual formula is =COUNTIFS($A$1:$A,A1,$B$1:$B,B1), and the criterion obviously changes to A2, A3 etc depending on the row number. 

Can this be done in custom calculations in Looker?

Thanks in advance for any help!

0 2 6,434
2 REPLIES 2

jcdufault
Participant II

Hi Liam, 

There is no countif in looker but you could simulate it with a sum. Something like:

sum(if(${payments.amount} > 1000,1,0))

This evaluates the payment.amount, if it is greater than 1000, it returns 1 or else it returns 0. By summing the 1s you simulate a count.

Hope this helps! 

Hi Liam.Thomas,

Yes , you can try above solution provided be jcdufault.

I have also used this in past , it has worked as expected .

syntax- 

sum(if(${table.column} > 100,1,0))
Top Labels in this Space
Top Solution Authors