Create a Summary Report from a Multi-select Dropdown Field

Question

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

quickbasecoachd · Answer

You can do this with three fields.The formula for [A count] would beIf(Contains(ToText([my multi select field],"A"),1,0)But be sure that your choices are unique like ABC and not likeMiceElaphantsAntsBecause the phrase ants is also in elephants.Then do similar fields for the [B count] and [C Count]

_anomdiebolt_ · Answer

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.

paultrimor · Answer

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

adamkeever1 · Answer

That is very useful. How did you learn about this?

Forum Discussion

Create a Summary Report from a Multi-select Dropdown Field