Combining IF Blanks with CONCATENATE

[Adrac]

Hello All,

I have a working formula but would like to add in: If the cell is blank then write in "NoGroups"

Example: Result in Z2

 =CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2)

And brings me up the result I need but would like to add NoGoups to cell Z2

I thought of

IF(ISBLANK(Z2),"No Group"),(CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2)))

But this dosent work, Can you help?

 

[Joe4]

You closed out your IF statement before the last argument.
Try:

=IF(ISBLANK(Z2),"No Group",CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2))

 

[Adrac]

I think there is another mistake, the Z2 is where the formula is and is now coming back with "0" result. Is there a way of checking all the range in the Concat and give "No Group" if its blank?

 

[Joe4]

I missed the fact that you have a circular reference going on here. Your formula is referencing the same cell it is placed in. You usually want to avoid doing that.

One way of doing what you want:

=IF(CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2)="","No Group",CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2))

 

[Adrac]

Perfect, that's works. Thank you so much!

 

[Joe4]

You are welcome.

 

[jtakw]

Hi,

Unless the range O2:Y2 may contain formula blanks ( "" ), I think the formula can be shortened to this, in Z2 enter:

=IF(COUNTA(O2:Y2)=11,CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2),"No Group")