"Cannot compare List with Decimal "

AppSheet AppSheet Q&A
,
1

Hi, I am trying to count the number of times a value appears that is within a range of 2 other values. In Google Sheets I would use COUNTIFS, but in AppSheet I am struggling to find the solution. This is my Expression:

COUNTIFS(
SG Raw Within Filter Range[Conformance] >= LOOKUP( 2 , "Filter" , "_RowNumber" , "Lower Tolerance" )),

(SG Raw Within Filter Range[Conformance] <= LOOKUP( 2 , "Filter" , "_RowNumber" , "Upper Tolerance" ))

And this is the error:

Cannot compare List with Decimal in (SG Raw Within Filter Range[Conformance] >= ANY(SELECT(Filter[Lower Tolerance],([_RowNumber] = 2))))

Both columns are of decimal type…

2

Hi @jonbowles84

This is because this:

SG Raw Within Filter Range[Conformance]

will return a list of decimals.

while this:

ANY(SELECT(Filter[Lower Tolerance],([_RowNumber] = 2)))

will return a decimal.

You may benefit from using a dereferenced expression.

I suspect the LOOKUP expression you wrote, with the “2” argument, is because you are making switch the range validation depending on something else, but this is just an assumption.

Basically, a COUNTIF expression looks like this:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-FILTER-LOOKUP-MAXROW-MINROW-REF-ROWS-and-SELECT/m-p/366399

(post from @steve, may 11th 2020, 11:42 am)

Can you provide your table structure, so that we can help you efficiently ?

2 Likes