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]

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.

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

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

 

[thoart]

Thank you Aladin. Its working. I only have one question: I have a product name that are very similar that I dont need in the result cell formula. Eks: Mikes. Is there a way to add in the formula to minus "Mikes" so that the formula dont search for it.

 

[Aladin Akyurek]

Thank you Aladin. Its working. I only have one question: I have a product name that are very similar that I dont need in the result cell formula. Eks: Mikes. Is there a way to add in the formula to minus "Mikes" so that the formula dont search for it.

Care to show the way each of both mikes and mike occur in C63:C72?

 

[thoart]

Yes it does so Aladin. It search for words that at least contain the word "Mike" so the word "Mikes" is a true value. But I want it to work this way I only want to make a limitation. So I basicly only need something extra where I can write words/names that it should ignore. Ex; search for Mike, Jack, Jane but not Mikes. I have made an counting formula where I did this but cant get it to work with the formula you made.

=SUM(COUNTIFS(C63:C72,{"*mike*","*jack*","*jane*","*dobbs*","*arthur*"}))-SUM(COUNTIFS(C63:C72,{"*mikes*"}))

 

[Aladin Akyurek]

Yes it does so Aladin. It search for words that at least contain the word "Mike" so the word "Mikes" is a true value. But I want it to work this way I only want to make a limitation. So I basicly only need something extra where I can write words/names that it should ignore. Ex; search for Mike, Jack, Jane but not Mikes. I have made an counting formula where I did this but cant get it to work with the formula you made.

=SUM(COUNTIFS(C63:C72,{"*mike*","*jack*","*jane*","*dobbs*","*arthur*"}))-SUM(COUNTIFS(C63:C72,{"*mikes*"}))

But I wanted to know how these names occur C63:C72? Care to post a few rows from C63:C72?

 

[thoart]

Hey Aladin. My apologies for late answer. I have made a test sheet and give you the dropbox link here so you can see it. Basicly I only need to add a rule to ignore all words starting with ex; "PCS".

https://www.dropbox.com/sh/25x5yj52dmbqj3z/AABjauc8FqYRCbG9Yk84RqCLa?dl=0

 

[Aladin Akyurek]

Hey Aladin. My apologies for late answer. I have made a test sheet and give you the dropbox link here so you can see it. Basicly I only need to add a rule to ignore all words starting with ex; "PCS".

https://www.dropbox.com/sh/25x5yj52dmbqj3z/AABjauc8FqYRCbG9Yk84RqCLa?dl=0

Control+shift+enter, not just enter:

1.

=SUM(IF(ISNUMBER(SEARCH({"cable","tv","pc"},SUBSTITUTE(UPPER(A10:A17),"PCS",""))),1))

2.

=TEXTJOIN(", ",TRUE,IF(FREQUENCY(IF(1-(A10:A17=""),IF(ISNUMBER(SEARCH({"cable","tv","pc"},SUBSTITUTE(UPPER(A10:A17),"PCS",""))),MATCH(A10:A17,A10:A17,0))),ROW(A10:A17)-ROW(A10)+1),A10:A17,""))

 

[thoart]

You are the best Aladin. Big thanks You even made it not to count double kinds witch is good Just a little question. If I save the document and then open it again to add or remove a product then I get ",,,,,," with the results. Is this something you could fix quick? If not I am thankful anyway for what you have done so far

 

[Aladin Akyurek]

You are the best Aladin. Big thanks You even made it not to count double kinds witch is good

You are welcome.

Just a little question. If I save the document and then open it again to add or remove a product then I get ",,,,,," with the results. Is this something you could fix quick? If not I am thankful anyway for what you have done so far

Not sure I get this for I don't expect such a result...

 

[thoart]

I did notice that it didn't occur all time. If I add and remove different products randomly some times in different cells from A10:A17. Then I sometimes get the ", , , , " in C3 and/or C4.