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]

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.

I experimented a bit but this issue did not show up. Did you modify the formulas in some way?

 

[thoart]

Just modify the products but that was ofcourse withing the "" and it works. But maybe there is something corrupt with my office install or something. I just changed the ", " to " " between the products and that solved it even if it would be best with ", ". But I can live with that Thank you

Maybe I shouldnt ask another question in this post but I made a formula to give a certain text in a cell according to how the information are in L239:W253 and L292:W306 (merged cells). The formula works but formula section IF(ISTEXT(L239:W253) only works when the text are in L239, if I write something only in L240 then it doesent take action.

=IF(ISNUMBER(SEARCH("not approved",L239:W253)),"The product are not approved.",IF(ISTEXT(L239:W253),"approved product, see recommendation.",IF(ISTEXT(L292:W306),"approved product, see recommendation",IF(ISBLANK(L239:W253),"Approved product."))))

 

[Aladin Akyurek]

Just modify the products but that was ofcourse withing the "" and it works. But maybe there is something corrupt with my office install or something. I just changed the ", " to " " between the products and that solved it even if it would be best with ", ". But I can live with that Thank you

Thanks for the update. If you ever resolve the mystery, I'd like to hear about it.

 

[Aladin Akyurek]

[...]
Maybe I shouldnt ask another question in this post but I made a formula to give a certain text in a cell according to how the information are in L239:W253 and L292:W306 (merged cells). The formula works but formula section IF(ISTEXT(L239:W253) only works when the text are in L239, if I write something only in L240 then it doesent take action.

=IF(ISNUMBER(SEARCH("not approved",L239:W253)),"The product are not approved.",IF(ISTEXT(L239:W253),"approved product, see recommendation.",IF(ISTEXT(L292:W306),"approved product, see recommendation",IF(ISBLANK(L239:W253),"Approved product."))))

What is merged exactly? L239 up to W239, i.e. per row?

 

[thoart]

My apology for explaining misleading. I will rather just explain how I need it The numbers below at the begining is refering to the order. If Nr.1 and Nr.3 are both TRUE then Nr.1 is the one who take action. Hope I explained it. The formula I showed works but can't make it to work 100%.

1 - IF the cell range (L182:L196) contain the text "big house" then give text "The house is big."
2 - IF the cell range (L182:L196) AND (L235:L249) contain text in any cell, then give text "There is a text in the cell"
3 - IF the cell range (L182:L196) AND (L235:L249) contain no text at all, then give text "There is no text in the cell range."

 

[Aladin Akyurek]

My apology for explaining misleading. I will rather just explain how I need it The numbers below at the begining is refering to the order. If Nr.1 and Nr.3 are both TRUE then Nr.1 is the one who take action. Hope I explained it. The formula I showed works but can't make it to work 100%.

1 - IF the cell range (L182:L196) contain the text "big house" then give text "The house is big."
2 - IF the cell range (L182:L196) AND (L235:L249) contain text in any cell, then give text "There is a text in the cell"
3 - IF the cell range (L182:L196) AND (L235:L249) contain no text at all, then give text "There is no text in the cell range."

Like this:

=IF(COUNTIFS(L182:L196,"big house"),"The house is big.",LOOKUP(SUM(COUNTIFS(L182:L196,"?*"),COUNTIFS(L235:L249,"?*")),{0,"There is no text in the reference";1,"There is text in the reference"}))

 

[thoart]

Yes. Thanks again. Must say that you do the job with the shortest formula

 

[Aladin Akyurek]

Yes. Thanks again. Must say that you do the job with the shortest formula

You are welcome.

 

[thoart]

Just a quick add question if you have time ofcourse. Don't mean to ask to much.

You gave me this formula that are under here. Any chance to expand the formula to avoid two words. The formula under only avoid "PCS". Ex: it could avoid "PCS" AND "LPS".

• =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,""))​
  
  
  
  

 

[Aladin Akyurek]

Just a quick add question if you have time ofcourse. Don't mean to ask to much.

You gave me this formula that are under here. Any chance to expand the formula to avoid two words. The formula under only avoid "PCS". Ex: it could avoid "PCS" AND "LPS".

• =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,""))​

Control+shift+enter, not just enter:

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