CONCAT only IF spesific cells contain spesific words/names!

[thoart]

HeyI've tried to make a formula but can't get it to happend. I use a formula to count words/names and it works fine:=SUM(COUNTIFS(C63:C72,{"*Mike*","*Peter*","*Jane*","*Even*","*Jack*"}))But my problem is that beside counting the words then I also neet to have the types beside.Eks; cell A1 counts all the spesific names I have according to the rule above and B2 show alle the names in the cell. So if there is more names that are not listed in the rule then it should not be counted or listed.A1 B25 Mike, Peter, Jane, Even, JackCan someone help?

 

[Aladin Akyurek]

As I understand it, you have in A2:

=SUM(COUNTIFS(C63:C72,{"*Mike*","*Peter*","*Jane*","*Even*","*Jack*"}))

and you want the corresponding names in B2...

In B2 control+shift+enter, not just enter:

=TEXTJOIN(", ",TRUE,IF(FREQUENCY(IF(1-(C63:C72=""),IF(SUM(COUNTIFS(C63:C72,{"*Mike*","*Peter*","*Jane*","*Even*","*Jack*"})),MATCH(C63:C72,C63:C72,0))),ROW(C63:C72)-ROW(C63)+1),C63:C72,""))

TEXTJOIN() is available on 2016 Excel versions...

 

[thoart]

As I understand it, you have in A2:=SUM(COUNTIFS(C63:C72,{"*Mike*","*Peter*","*Jane*","*Even*","*Jack*"}))and you want the corresponding names in B2...In B2 control+shift+enter, not just enter:=TEXTJOIN(", ",TRUE,IF(FREQUENCY(IF(1-(C63:C72=""),IF(SUM(COUNTIFS(C63:C72,{"*Mike*","*Peter*","*Jane*","*Even*","*Jack*"})),MATCH(C63:C72,C63:C72,0))),ROW(C63:C72)-ROW(C63)+1),C63:C72,""))TEXTJOIN() is available on 2016 Excel versions...

-------------------------------------Thank you for quick reply. Cell A1 with the counting is working perfectly. But A2 has the job to name all the words/names that A1 is counting. So if I write i Mike in C63, Peter in C65 and Arthur in C66 then A1 should be giving the number 2 and A2 should have the content Mike, Peter.I couldnt get your code to work.

 

[Peter_SSs]

TEXTJOIN() is available on 2016 Excel versions...

For the record it's some 2016 versions. I have a stand-alone version and many of the new Excel 2016 functions are not available to me.

 

[Aladin Akyurek]

For the record it's some 2016 versions. I have a stand-alone version and many of the new Excel 2016 functions are not available to me.

Yep, no idea which are and which are not blessed...

 

[thoart]

I have Microsoft Office 365 ProPlus. To explain to more easy. The only thing I need help with is just the CONCAT formula. This formula does the work; =IF(ISTEXT("C63:C65"),CONCATENATE(C63," ",C64," ",C65),"") but the only problem is that I need the formula to have limitation. I only want it to "pick up" the spesific names/word not everything. So can someone help me to add a IF statement or something to make it only find the words for example; apple and banana.

 

[Aladin Akyurek]

I have Microsoft Office 365 ProPlus. To explain to more easy. The only thing I need help with is just the CONCAT formula. This formula does the work; =IF(ISTEXT("C63:C65"),CONCATENATE(C63," ",C64," ",C65),"") but the only problem is that I need the formula to have limitation. I only want it to "pick up" the spesific names/word not everything. So can someone help me to add a IF statement or something to make it only find the words for example; apple and banana.

Do you have TEXTJOIN or not?

 

[thoart]

I do have TEXTJOIN in my version of Excel.

 

[Aladin Akyurek]

I do have TEXTJOIN in my version of Excel.

Right. I understood your question as follows:

C63:C72 contains names.

You have a COUNTIFS formula, i.e.

=SUM(COUNTIFS(C63:C72,{"*Mike*","*Peter*","*Jane*","*Even*","*Jack*"}))

which totals the counts of the occurrences of mike, peter, jane, even, and jack.

And you want in B2 a text string showing which names make up the total. If this is a correct reconstruction of what is desired, see:

https://dl.dropboxusercontent.com/u/65698317/thoart TEXTJOIN.xlsx

 

[thoart]

Thank you Aladin. You understood it and the formula is very close. The only thing is that when I write a name that is not in the formula then the COUNTIFS formula is adding the unknown name.