Is there any way I can do countifs() function...

(Administrator) #1

Is there any way I can do countifs() functions directly in AppSheet. I need to count something based off of 3 criteria. For example, I need a formula that examines the following sheet and gives me a running count based off of 3 columns.

  1. a specific user (i.e. useremail())

  2. a specific type of service (i.e. Type 1) 3. has a specific status (i.e. Pending).

I’ve tried using the count(select(in())) function in a virtual column, but I can only get it return a single column of data, or compare a single “value” when counting. I can’t seem to nest it with “if()” or “and()”. Any ideas?

Thanks!

(Tony Fader) #2

@Administrator1 There’s no COUNTIF in AppSheet, but you can use a COUNT(SELECT(…)) formula. See this app for an example: appsheet.com - SUMIF and COUNTIF - This app shows how to do SUMIFs and COUNTIFs in AppSheet

More info here: https://help.appsheet.com/expressions/expression-types/list-expressions-and-aggregates SUMIF and COUNTIF - This app shows how to do SUMIFs and COUNTIFs in AppSheet appsheet.com

(Tony Fader) #3

As a starting point, you’ll want something like

COUNT(SELECT(YourTable[Key Column], AND([Column1] = “some value”, [Column2] = "another value)))

(Administrator) #4

I tried using the Count(Select()) method outlined in the example application, but it doesn’t work properly for my purpose (unless I’m missing something). The Count(Select()) method only compares a single range and criteria, not multiple. For instance, I need a formula that does the following in Excel: COUNTIFS([Username],USEREMAIL(),[Service Type],“Type 2”,[Status],“Pending”). COUNTIFS, unlike COUNTIF, allows you to use multiple criteria ranges and values to create a count.

Here’s a link to the function: support.office.com - COUNTIFS function - Office Support

COUNTIFS function - Office Support support.office.com