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

Administrator1 · 01-30-2019 04:00 PM

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.

a specific user (i.e. useremail())
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!

tony1

@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

tony1

As a starting point, you’ll want something like

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

Administrator1

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

Shazwina_Zakari

Hi, did you get the solution to COUNTIFS()? I’m having the same problem

WillowMobileSys

Welcome to the AppSheet Community!

I believe that you can use a combination of IFS(), COUNT() and SELECT() functions like so:

IFS(
<criteria1>, COUNT(SELECT(...)),
<criteria2>, COUNT(SELECT(...)),
<criteria3>, COUNT(SELECT(...)),
<criteria4>, COUNT(SELECT(...)),
true, <default value or expression>
)

The criteria can be expressions returning Yes/No results like AND(), OR(), IN(), etc.

When using IFS(), I strongly recommend using a default value of some sort as shown. It can save you some troubleshooting grief later.