Skip to main content
Question

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
    This topic has been closed for 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.
        Terms & ConditionsAccessibility statement