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

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

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. :frowning:

1 Like

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]))
)

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:


1 Like

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

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

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

1 Like