Can I turn formula fields using IF(FIND) into a numeric field? (Or alternatives)

[Adi_Menayang]

Hello,

I’m trying to summarize information in a Linked Record field type collected through Forms in which respondents can select multiple linked records. We invite respondents to select their Subject Area Expertise, options provided are categories from a sector-wide set of standard taxonomy.

The Subject Area taxonomy is organized in a hierarchical manner. For example:

• Level 1 (Category): Health (SE000000)
• Level 2: Nursing Care (SE060000)
• Level 3: Home Health Care (SE60100)
• Level 3: Hospice Care (SE60200)

Respondents can select from the whole list without seeing the hierarchy. I would like to generate charts that summarize and collapse respondents’ answers. Using the example above, I would like the chart to collapse all individuals who selected “Home Health Care” and “Hospice Care” to fall under the bar/pie chart slice for "Health"

I tried using a lookup field for each Subject Area’s category, but it duplicates responses
In the example below, the row with three different “Environment” subject area will contribute to three counts of “Environment” in a chart I generate on Interfaces or the Chart block in Apps. I want it to reflect as one because all three belong to one individual/row.

I created binary dummy columns, but I can’t summarize these
My workaround was to use the Number summary of formula field results. Each Level 1 category has its own formula field, where the formula is:

IF( FIND(“SE”, {BACKEND_Expertise Category Prefix (from Subject Area Expertise)}), ‘1’, ‘0’)

(the prefix is a lookup from the table where Subject Areas are saved).

But the formula field isn’t considered numeric, so I can’t create summaries. Two questiuons:

• Is there a way to make this numeric?
• Any other suggestions on how I can summarize my respondents by their Level 1 subject areas selected in the forms for charts and graphs?

Thank you!!

[Kamille_Parks · Accepted solution]

Adi_Menayang:

I tried using a lookup field for each Subject Area’s category, but it duplicates responses

Have you tried using a Rollup field with the ARRAYUNIQUE(values) aggregation instead?

Adi_Menayang:

But the formula field isn’t considered numeric

That’s because you put your numbers in quotes. "1" is essentially "one" whereas 1 is a true numeric value.

[Adi_Menayang]

Kamille THANK YOU!

I was not aware of the ARRAYUNIQUE formula for rollup, and good to know that I don’t need quotations for the numbers.

ARRAYUNIQUE is going to help me a LOT in terms of analysis and deleting all these helper columns I’ve created. I am so thankful.

[Adi_Menayang]

@Kamille_Parks seems like the output for ARRAYUNIQUE cannot be split when I generate a bar/pie chart in the Charts app or in Interface (e.g. “Health, Education” and “Health” are separate bars/slices.

Is there a way to manipulate the formula to make them splittable when generating a chart?