PaulTrimor
7 years ago

# Create a Summary Report from a Multi-select Dropdown Field

Suppose I have a Multi-select field with options (A, B, C) and 100 records where the field is used.

Is it possible to create a report that counts each time an option is selected in each record?
For example:

A - 5
B - 8
C - 2

Above would indicate that the option A has been selected 5 times out of all of my records, B has been selected 8 times, and C has been selected 2 times.

When I create a summary report for each I just get the count of each combination. Like

A           - 5
A;B        - 3
A;B;C    - 2

### 4 Replies

• You can do this with three fields.
The formula for [A count] would be

If(Contains(ToText([my multi select field],"A"),1,0)

But be sure that your choices are unique like ABC and not like

Mice
Elaphants
Ants

Because the phrase ants is also in elephants.

Then do similar fields for the [B count] and [C Count]
• If you suffix and prefix the multi-select text field and the option string with the three characters " ; " (ie space, semicolon and space) you can absorb the oddball case where one option is a sub-string of another:

`ToNumber(Contains(" ; " & [field] & " ; ", " ; dog ; "))ToNumber(Contains(" ; " & [field] & " ; ", " ; cat ; "))ToNumber(Contains(" ; " & [field] & " ; ", " ; fox ; "))ToNumber(Contains(" ; " & [field] & " ; ", " ; ox ; "))`

In the above I used ToNumber() instead of Mark's If() to convert from text to number.