Sum If and Concatenate

MikeBerlanguet

New Member
Joined
Aug 26, 2016
Messages
1
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?

[TABLE="width: 224"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: center"]A
Team #
[/TD]
[TD="align: center"]B
Player
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]glen[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]paul[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Mike[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][/tr][tr][td]
1​
[/td][td]Team #[/td][td]Player[/td][td]concat[/td][/tr]
[tr][td]
2​
[/td][td]
1
[/td][td]glen[/td][td], glen, paul, mike[/td][/tr]
[tr][td]
3​
[/td][td]
1
[/td][td]paul[/td][td], paul, mike[/td][/tr]
[tr][td]
4​
[/td][td]
1
[/td][td]mike[/td][td], mike[/td][/tr]
[tr][td]
5​
[/td][td]
2
[/td][td]dan[/td][td], dan, damon[/td][/tr]
[tr][td]
6​
[/td][td]
2
[/td][td]damon[/td][td], damon[/td][/tr]
[tr][td]
7​
[/td][td="bgcolor:#D0CECE"][/td][td="bgcolor:#D0CECE"][/td][td="bgcolor:#D0CECE"][/td][/tr]
[tr][td]
8​
[/td][td][/td][td]
1
[/td][td]glen, paul, mike[/td][/tr]
[tr][td]
9​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]


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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,695
Messages
6,192,478
Members
453,727
Latest member
tuong_ng89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top