Doing something like COUNTIFS with an offline app

[maranasheets009]

I have this working beautifully with a Google Sheet that I migrated, but need it to run with AppSheet formulas since I'll be running in offline mode.

I have 2 tables.
We'll call one table "Values" - it has a single column that users enter a value in. These can be from zero to 20 and will contain duplicates.
The second table is called "Ranges" and has 7 columns, N1, N2, N3, etc.  None of this data will change. Each row has 7 values that correspond to the values that the users will enter into the Values table. At the end of the row is a SCORE column that should total the number of times the values in that row appear in the Values table.

I've tried using COUNT() with SELECT(), FILTER(), IN and some other options but I fail to get any results.

So what I'm looking for is:
For each row in the Ranges Table, a score that tells me the total number of times that the 7 numbers  in that row appear in the Values Table. The Ranges table will sort the rows based on this score. This score needs to update each time a new value is entered.

Can anybody help get me there?

[TeeSee1]

If I read your requirements right try the code below

IF(IN([N1], values[value]), 1, 0) +
IF(IN([N2], values[value]), 1, 0) +
.
.
IF(IN([N7], values[value]), 1, 0)

[maranasheets009]

Thanks for the assist, but no, that's not it.

[maranasheets009 · Accepted solution]

Maybe others have the same issue when trying to write COUNTIFS type formulas purely in AppSheet functions. Here's what worked for me.

You can see above I have 2 tables: Values and Ranges

In the Ranges table I created a virtual column with the following formula:

COUNT(SELECT(Values[InputCol], OR ([InputCol]=[_ThisRow].[N1],[InputCol]=[_ThisRow].[N2] **and so on with the rest of the columns**)))

This formula reads the value in each of the columns in the Ranges table and compares it to the user entered values in the Values table. Using the OR operator, if any of the conditions are true, that row gets counted.