Google Sheets - Formula to Concatenate Text in Range of Cells based on Numeric Value in Corresponding Range of Cells

Question

I have this setup in Google Sheets:

Row 1 is a list of ingredients. Rows 2-6 are specific plans that contain combinations of ingredients A-F - if there is a 1 in the column, it contains the ingredient in the column.

In column H, I want to concatenate the letters in row 1 if there is a 1 underneath it. So the formula I'm looking for in Col H should spit out the following:

Cell H2 --> A + B
Cell H3 --> A + C
Cell H4 --> A + B + D
Cell H5 --> A + C + D
Cell H6 --> A

I've tried the TEXTJOIN formula you see in the formula bar but it's not working. Can anyone help with this?

Thanks!

score 2 · Answer 1 · edited Sep 17 '20 at 19:59

2

Nevermind, I just figured it out. I had to change my formula above to:

=textjoin(" + ", TRUE, arrayformula(IF($B7:$G7=1, $B$1:$G$1,"")))

edited Sep 17 '20 at 19:59

TheMaster

45,448
6
62
85

answered Sep 17 '20 at 18:52

Anthony

61
1
5

3

You can also use: `=textjoin(" + ", TRUE, Filter($B$1:$G$1,$B7:$G7=1))` – Scott Craner Sep 17 '20 at 18:56
oh wow, I never knew about the Filter function in Sheets - much more intuitive. Love it. Thanks! – Anthony Sep 17 '20 at 19:05

score 0 · Answer 2 · answered Aug 05 '21 at 14:07

Anthony,

If I got it correctly what you want to combine, another option to do that is to use CONCATENATE + IF.

I needed to combine 5 columns but make a note while a cell contains particular data (e.g., when X is in the cell, not to combine columns but write a comment). And the combination of CONCATENATE + IF worked.

I found this solution here.

Hope it'll be somehow helpful :)

Google Sheets - Formula to Concatenate Text in Range of Cells based on Numeric Value in Corresponding Range of Cells

2 Answers2