Counting items within a multiple select field

Hi there,
I have a People table with a single select Age category (e.g., Boomer). Each person has chosen multiple Trends, which is a multiple select category linked to another table.

In the Trends table, there is a column with each Person listed. I can create a basic count of People, but am trying to segment this by age group. E.g., what are the Trend counts for Boomers?

Thank you so much for helping! I made some entertaining dummy data tables to illustrate this, but the forum won’t let me add images or links for some reason.

These are the links with the Dropbox part chopped off:
com/s/09mfrjvrpvogt0f/Screenshot%202019-07-24%2014.48.55.png
com/s/b5t9d2tplam7jt6/Screenshot%202019-07-24%2014.48.46.png

Welcome to the community, Chris! :smiley: The image/link restriction is likely because you’re early in your forum use. If you do a lot more here, the system will automatically remove those restrictions.

In your [Trends] table, add a rollup field named {Ages}, drawing from the links in the {People} field, and pulling in their age, using the following aggregation function: ARRAYJOIN(values, “”). For the first row, this will give you something looking roughly like this: BoomerMillenialMillenialSilent (the order may be different).

Next, add a formula field named {Boomer Count} (or just {Boomers} if you prefer), containing the following formula:

LEN(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(
                        Ages & "",
                        "Boomer",
                        "X"
                    ),
                    "Millenial",
                    ""
                ),
                "Gen X",
                ""
            ),
            "Gen Y",
            ""
        ),
        "Silent",
        ""
    )
)

In short, this will replace all instances of “Boomer” with an X, remove the rest, then get the length of the result, which will be the count of Boomers matching that trend. (Note: this assumes that your screenshot showed all possible age options. If not, add more nested SUBSTITUTE functions as needed.)

Duplicate this field, then change the formula so that one of the other age names is replaced by Xs, and Boomer is removed. Name that field to match the one being counted. Lather, rinse, and repeat until you have each age counted in its own field.

Thanks, Justin. Appreciate it. Will give it a shot and see how it goes!

Update for anyone in the same situation: the pivot table block can do these breakdowns (e.g., “Trends by Age Group”) and others fairly easily, if upgrading to Pro is an option for you.

1 Like

I haven’t used the Pivot Table block yet, mainly because I’ve only sparsely used pivot tables in the past, so I didn’t think to try it. Good to know it’s another option!