I’ve been asked to find a way to duplicate the CountUnique() function from Gsheets into a form in AppSheet… and it’s proving to be more difficult than I was hoping.

Consider this example:

I’ve got a form with 10 questions, each a number entry where people can enter any number they wish. At the end of the form, I need a count of each unique number entered by users; if they enter 10 unique numbers, I need a number result of 10, if they enter 4 unique, but the rest are duplicates - I need a number result of 4.

When you try and think about a formula to make this happen, it leads me to roads like this:

list(

[Column1],

if([Column2] = [Column1], “”, [Column2]),

if(in([Column3], list([Column1], [Column2])), “”, [Column3]),

etc… you see where this is going, for each column I have to create this conditional statement that looks at everything that has come before; this way each column only adds itself to the list if it’s value is not already present.

*This could easily become a monster of a formula.*

And this doesn’t take into consideration that now my list will have a blank (which I can remove), but in this example the column is a number - so I’m not sure if the blank would work, it might require a number in which case I’d have to come up with some Place_Holder number and hope no one enters that number.

Unless I’m overthinking things (happens a lot ), I can’t think of a more efficient way of accomplishing this - unless there was a way to reduce the list to unique values.

As always, thanks for considering!!

*(And sorry if this is a duplicate, I tried looking but couldn’t find anything about this; only stuff about uniqueID)*

*PPS: Upon further thinking, the CountUnique could be removed from this request as I could just wrap a COUNT() around the unique() of my list. *