Sum If and Concatenate

[MikeBerlanguet]

Hi,

Probably a simple solution but here is the question:

I want to concatenate text in cells that are in the " sum range" of a Sum If formula. I would like excel to take the names from Team #1 (column B) and put them in a single cell: glen, paul, mike Thought a sumIf might do it but can't seem to get it right. Any suggestions?

A Team #	B Player
1	glen
1	paul
1	Mike
2
2

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Michael M]

You may need to use a Custom Function
Have a look here for the UDF
AND
a sample workbook to play with


Get Multiple Lookup Values in a Single Cell in Excel

 

[Aladin Akyurek]

Also:

1. You can try a formula with TEXTJOIN on a 2016 system.

2. Run a generic UDF like ACONCAT for which you can find a lot of posts where it is used.

3. Or run the Hong Kong method:

Row\Col	A​	B​	C​
1​	Team #	Player	concat
2​	1​	glen	, glen, paul, mike
3​	1​	paul	, paul, mike
4​	1​	mike	, mike
5​	2​	dan	, dan, damon
6​	2​	damon	, damon
7​
8​		1​	glen, paul, mike
9​

In C2 enter and copy down:

=", "&$B2&IFERROR(VLOOKUP($A2,CHOOSE({1,2},$A3:$A$7,$C3:$C$7),2,0),"")

In C8 enter:

=REPLACE(VLOOKUP(B8,$A$2:$C$6,3,0),1,2,"")

which is the result cell.

 

[tygrrboi]

If you do not have a LOT of cells and you do not mind a long formula..

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}span.s7 {color: #fe4fdd}</style>=TRIM(CONCATENATE(
IF(A1=ROWS($A$1:$A1),B1,"")," ",
IF(A2=ROWS($A$1:$A1),B2,"")," ",
IF(A3=ROWS($A$1:$A1),B3,"")," ",
IF(A4=ROWS($A$1:$A1),B4,"")," ",
IF(A5=ROWS($A$1:$A1),B5,"")," ",
IF(A6=ROWS($A$1:$A1),B6,"")))


If you drag this formula down, the next cell will list all people from team 2, and team 3 etc.
but you do need to copy and paste the if function for each name you need to consider (each person in column B)

=TRIM(CONCATENATE(
IF(A1=ROWS($A$1:$A1),B1,"")," ",
IF(A2=ROWS($A$1:$A1),B2,"")," ",
IF(A3=ROWS($A$1:$A1),B3,"")," ",
IF(A4=ROWS($A$1:$A1),B4,"")," ",
IF(A5=ROWS($A$1:$A1),B5,"")," ",
IF(A6=ROWS($A$1:$A1),B6,"")," ",
IF(A7=ROWS($A$1:$A1),B7,"")," ",
IF(A8=ROWS($A$1:$A1),B8,"")," ",
IF(A9=ROWS($A$1:$A1),B9,"")," ",
IF(A10=ROWS($A$1:$A1),B10,"")," ",
IF(A11=ROWS($A$1:$A1),B11,"")," ",
IF(A12=ROWS($A$1:$A1),B12,"")," ",
IF(A13=ROWS($A$1:$A1),B13,"")," ",
IF(A14=ROWS($A$1:$A1),B14,"")," ",
IF(A15=ROWS($A$1:$A1),B15,"")," ",
IF(A16=ROWS($A$1:$A1),B16,"")," ",
IF(A17=ROWS($A$1:$A1),B17,"")," ",
IF(A18=ROWS($A$1:$A1),B18,"")," ",
IF(A19=ROWS($A$1:$A1),B19,"")," ",
IF(A20=ROWS($A$1:$A1),B20,"")," ",
IF(A21=ROWS($A$1:$A1),B21,"")," ",
IF(A22=ROWS($A$1:$A1),B22,"")," ",
IF(A23=ROWS($A$1:$A1),B23,"")," ",
IF(A24=ROWS($A$1:$A1),B24,"")))

for example.