IF with multiple logical tests with text displayed if each true

Originally Posted by danielball

there are many more than three cells but that's just as an example.

You will need a VBA function to do this.

Are you open to that idea?

Assuming your "names" are just to illustrate, see if this is something you can build on....

	A	B
1	1	AAA
2	1	BBB
3	1	CCC

B1=IF(A1=1,"AAA","")
B2=IF(A1=1,"BBB","")
B3=IF(A1=1,"CCC","")

=SUBSTITUTE(TRIM(B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10)," ",",")
(THis method cam from another member here - Martin, I think)

if A1 and A2 both contain 1 then I want "London, Paris" displayed in the cell

They want comma delimiters which makes it much more difficult over a large range of cells.

Try this...

With your file open...

Press the key combination of Alt+F11 to open the Visual Basic Editor

Goto the menu Insert > Module

Copy the VBA code from this post and paste it into the window that opens on the right side of the screen.

Press the key combination of ALT+Q to close the Visual Basic Editor and return to Excel.

Let's assume your data looks something like this:

Data Range

	A	B
1	Header	Header
2	1	London
3	1	Paris
4		Berlin
5		Boston
6	1	Pittsburgh
7		Madrid
8		Rome
9		Sydney
10	1	Chicago

Enter this array formula** in D2:

=concatall(IF(A2:A10=1,B2:B10,""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This will be the result:

Data Range

	A	B	C	D
1	Header	Header	------	Concatenated
2	1	London		London, Paris, Pittsburgh, Chicago
3	1	Paris
4		Berlin
5		Boston
6	1	Pittsburgh
7		Madrid
8		Rome
9		Sydney
10	1	Chicago

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.