0

I have been trying to use a Text-join formula to pull together comments based on whether there is certain text in adjacent cells. I've used this formula which has worked fine:

=TEXTJOIN(" "&CHAR(10)&CHAR(10),"TRUE",IF(CA2:CA100="Yes",BZ2:BZ100,""))

However, is there a way to stop the formula returning a "0" if the cell in column BZ is blank?

I've looked at other threads and tried the following but can't seem to get it right?

=TEXTJOIN(" "&CHAR(10)&CHAR(10),"TRUE",IF(AND(CA2:CA100="Yes",BZ2:BZ100<>""),'Main Data'!BZ2:BZ100,""))

Many thanks!

JNevill
  • 46,980
  • 4
  • 38
  • 63
RM86
  • 3
  • 1
  • 3
  • Coming late to the party with a UDF solution [here](https://stackoverflow.com/questions/50716550/textjoin-for-xl2013-with-criteria/50719050#50719050). –  Jun 08 '18 at 02:33

1 Answers1

0

Array formula do not like AND, use * instead:

=TEXTJOIN(" "&CHAR(10)&CHAR(10),"TRUE",IF(('Main Data'!CA2:CA100="Yes") * ('Main Data'!BZ2:BZ100<>""),'Main Data'!BZ2:BZ100,""))

And remember to use Ctrl-Shift-Enter

Scott Craner
  • 148,073
  • 10
  • 49
  • 81