Help Summarizing Columns

Lauren Y

Thanks for your help!

I am looking to summarize in one column all of the reasons why something is red. For instance in row 2, I would like "why red" to be "Financial, Resources" because both of those two have red selections.

Other than creating a helper column for each health status then joining them, is there a more efficient way to do this? TIA!

jmyzk_cloudsmart_jp

Hi @Lauren Y

I would use the MATCH function to find the first "Red" for the RGY range.😁

Then, using the match position, use the INDEX function to look up the "Health Type" range.

I used the first row in my example, but if you want to use the column formula, you must prepare another sheet with a number column and a Health Type column.

If you want to find out all the "Red" as the Reason, first concatenate with the JOIN function and with the SUBSTITUTE function, convert "Red" to "R," "Green" to "G" and "Yellow" to "Y."

The conversion will give you something like "GGRYGRY."

Then, with the FIND function, find the second position of "R."  

(If you want the third position or Health Type, do the same.)

https://app.smartsheet.com/b/publish?EQBCT=017344e40f0d4a16b5fc4257de4a1a71