How do I summarize text data from multiple cells into one cell only if criteria is met in each cell?

alaub29

I am sending a form out to associates in which I ask them to rate their familiarity with multiple subjects. The 3 available choices for them to choose from are: Very Familiar, Somewhat Familiar and Not Familiar. I would like to summarize their responses from multiples cells (Sales Orders, Invoicing, Stock Status, Purchase Orders) in 1 summary cell (PU Summary) only if they choose "Not Familiar" as the response by listing the subject in the summary cell.

In my example, if they choose Very Familiar or Somewhat Familiar, nothing will go in to the PU Summary cell.

Gillian C

Hi @alaub29

You could try the following:

=IF([Sales Orders]@row = "Not Familiar", "SOs", "") + " " + IF([Invoicing]@row = "Not Familiar", "Inv", "") + " " IF([Stock Status]@row = "Not Familiar", "SS", "") + " " IF([Purchase Orders]@row = "Not Familiar", "POs", "")

I have used Inv as the short code for Invoicing and SS as the short code for Stock Status.

Hope this helps :)

Gillian C

Hi @alaub29

You've missed a + on the second line of the screengrab you added. After the IF for Invoicing you need + " " + IF([Stock Status…

and then again before the IF([Purchase Orders]@row…

and it should work now.

#UNPARSEABLE normally means there is something wrong with the syntax of the formula, such as missing items or too many parenthesis

Paul Newcome

If you are using a multi-select dropdown column as in your screenshot, you will need to use line breaks in between instead of spaces to ensure each one is a separate "selection". This will ensure consistency for future reporting.

=IF([Sales Orders]@row = "Not Familiar", "SOs" + CHAR(10), "") + IF([Invoicing]@row = "Not Familiar", "Inv" + CHAR(10), "") + IF([Stock Status]@row = "Not Familiar", "SS" + CHAR(10), "") + IF([Purchase Orders]@row = "Not Familiar", "POs", "")

Gillian C

Hi @alaub29

You could try the following:

=IF([Sales Orders]@row = "Not Familiar", "SOs", "") + " " + IF([Invoicing]@row = "Not Familiar", "Inv", "") + " " IF([Stock Status]@row = "Not Familiar", "SS", "") + " " IF([Purchase Orders]@row = "Not Familiar", "POs", "")

I have used Inv as the short code for Invoicing and SS as the short code for Stock Status.

Hope this helps :)

alaub29

Hi @Gillian C . I tried that (Added "Never Do Task" part since that's the full prompt) but got #UNPARSEABLE

Gillian C

Hi @alaub29

You've missed a + on the second line of the screengrab you added. After the IF for Invoicing you need + " " + IF([Stock Status…

and then again before the IF([Purchase Orders]@row…

and it should work now.

#UNPARSEABLE normally means there is something wrong with the syntax of the formula, such as missing items or too many parenthesis

alaub29

@Gillian C Wow, that worked! Thank you soooo much! 😊

Gillian C

Hi @alaub29

No problem, glad it helped 😊

Paul Newcome

If you are using a multi-select dropdown column as in your screenshot, you will need to use line breaks in between instead of spaces to ensure each one is a separate "selection". This will ensure consistency for future reporting.

=IF([Sales Orders]@row = "Not Familiar", "SOs" + CHAR(10), "") + IF([Invoicing]@row = "Not Familiar", "Inv" + CHAR(10), "") + IF([Stock Status]@row = "Not Familiar", "SS" + CHAR(10), "") + IF([Purchase Orders]@row = "Not Familiar", "POs", "")

Gillian C

Hi @alaub29

Did you see the comment above from @Paul Newcome . Really helpful if you are using multi-select dropdown. Thanks Paul, learn something new every day :)

alaub29

Thank you to you both! This really saved me a major headache! @Gillian C @Paul Newcome

Paul Newcome

Happy to help. 👍️