Is there an easy way to count identical recor...

(Jonathon Sinclair) #1

Is there an easy way to count identical records in a manually generated list? I know how to do this across rows using SELECT() statements, etc.

I would like to count the total true/false answers for a survey. Each survey response creates a single row in the table, so I must manually generate a list using the question columns as such:

LIST([q1],[q2],[q3],[q4],[q5])

where each question can either be true or false

I would like to do something similar to below to return the total ‘TRUE’ answers:

COUNT(

LIST([q1],[q2],[q3],[q4],[q5]) - LIST(“false”) )

The problem is, these arguments appear first remove all duplicate values, meaning the above function will always count “1”, or the remaining “True” value after duplicates have been removed.

Also I cannot seem to user FILTER() arguments on a manually generated list as there is no table.

Any ideas?

(Aleksi Alkio) #2

How about…

COUNT(SPLIT([q1]&[q2]&[q3]&[q4],“TRUE”))-1

(Jonathon Sinclair) #3

Cheers Aleksi, elegant solution. Translate

(Aleksi Alkio) #4

You’re welcome