SOLVED

New Contributor

# Best way to populate 1 cell with a summary of several previous cells in the same row?

Hello,

I have data in some columns but not others, [data from bagel shop customers selecting their cream cheese options for orders]. What is the best way to populate 1 cell at the end of the row to summarize everything the user entered in the entire row, including the item name for each entry [which is in the row above], but also skip/ignore any blank entries?

A          B              C                      D                    E               F          G

 Cup of Plain Pint of Plain Cup of Cinnamon Sugar Pint of Cinnamon Sugar Cup of Garlic Cream Pint of Garlic Cream Summary 1 2 1 Cup of Plain, 2 Cup of Cinnamon Sugar

I was using Google Sheets and had a formula that worked there, but I can’t replicate in Excel.

Thanks!

Lindsey

Idea of the formula:

=TEXTJOIN(", ", TRUE, ARRAYFORMULA(IF(LEN(E2:M2),CONCAT(E2:M2,CONCAT(" ", \$E\$1:\$M\$1)), )))]

2 Replies
Best Response confirmed by gindseylaudet (New Contributor)
Solution

# Re: Best way to populate 1 cell with a summary of several previous cells in the same row?

As an array formula confirmed with Command+Return:

=TEXTJOIN(", ",TRUE,IF(A2:F2>0,A2:F2&" "&A\$1:F\$1,""))