Count occurrences in a table (CountIf) across rows & columns

[Presley_Thompso]

Hi there! Newbie to AppSheet and struggling with a basic CountIf. I have a table with multiple rows & columns that have the same drop-down options. I’d like to tally the occurrence of each selected across the table. So let’s say I have Table 1 with multiple columns that have a drop-down with the options Red, Blue, Green, I’d like a formula in Table 2 to count the number of cells where “Red” is selected in all of Table 1. I’m sure it’s simple, but struggling to find how in support docs.

Thanks!

Presley

[Steve]

AppSheet can not trivially count vertically: there’s no easy way to refer to columns 2 through 7. Instead, you have to reference each column individually: column2, column3, column4, …, column7. So to count the occurrences of Red in columns 2 through 7 in all rows of table 1:

COUNT(
  FILTER("Table 1", ("Red" = [Column 2]))
  + FILTER("Table 1", ("Red" = [Column 3]))
  + FILTER("Table 1", ("Red" = [Column 4]))
  ...
  + FILTER("Table 1", ("Red" = [Column 7]))
)

A table in AppSheet is more akin to a database table than a spreadsheet (despite often using a spreadsheet for data storage), so you’ll need to adopt a database perspective, unfortunately.

[Presley_Thompso]

Thanks Steve!

So next step: in Table 2 I have one column (named “Color”) that represents the potential options (Red, Blue, Green), and a 2nd column that I’d like to have this count from Table 1. So each row in column 2 would have the count of the column 1 value. I tried this (below) in column 2, but can’t seem to get it to work. Thoughts?

COUNT(
  FILTER("Table 1", ([Color] = [Column 2]))
  + FILTER("Table 1", ([Color] = [Column 3]))
  + FILTER("Table 1", ([Color] = [Column 4]))
  ...
  + FILTER("Table 1", ([Color] = [Column 7]))
)

[Steve]

You’ll need to dereference [_THISROW] to access the color in the Table 2 row:

COUNT(
  FILTER("Table 1", ([_THISROW].[Color] = [Column 2]))
  + FILTER("Table 1", ([_THISROW].[Color] = [Column 3]))
  + FILTER("Table 1", ([_THISROW].[Color] = [Column 4]))
  ...
  + FILTER("Table 1", ([_THISROW].[Color] = [Column 7]))
)

See also:

help.appsheet.com

Dereference Expressions

Use a dereference expression to retrieve a field value from a referenced record.

help.appsheet.com

FILTER()

Select rows of table or slice

[Presley_Thompso]

Hm, I had tried that first with no luck - I’ll take a look at the links you posted, thanks.

[Steve]

Ah, another point: the column to contain the count should be a virtual column for easiest implementation.

help.appsheet.com

Virtual Columns

Check out how you can use virtual columns to add more information to your app.

[Presley_Thompso]

Thanks! Those are helpful. Still wasn’t working, but I figured out my issue - I had columns titled with numbers (1, 2, etc), which apparently causes issues. Renamed and it seems to work fine. Appreciate the help!

Presley

[kasun]

I have a problem. can you help me.

Above is part of my table. I want to select “road No” and “description of Layer” from a dropdown list of a form (Like a filter) and count how many "description of Layer"s are selected for that “road No”. How to do it?