Express 'Joined up' IF's Q

[Sean]

How could I express the following as a Text string

I have 3 Names in A1:A3 (Jim; Bob; Mike); in B1:B3 is a value of
either 1 or 0 (1;0;1). I wish to construct an opening statement
depending if the values in B1:B3 are 1, so for example if B1=1;B2=0
and B3=1 my opening statement would be:

Dear Jim / Mike

if B1=0;B2=0 and B3=1 my opening statement would be:

Dear Mike

if B1=1;B2=1 and B3=1 my opening statement would be:

Dear Jim / Bob / Mike

if B1=0;B2=0 and B3=0 my opening statement would be:

<blank>

I have 5 names in total so haven't put all the permutations above,
perhaps there is not an easy way to do this

 

[David Biddulph]

=IF(B1+B2+B3,"Dear "&IF(B1,A1,"")&IF(B1*(B2+B3)," /
","")&IF(B2,A2,"")&IF(B2*B3," / ","")&IF(B3,A3,""),"")

 

[Sean]

Thanks David, can't seem to get to work

Is this part complete =IF(B1+B2+B3,"Dear "&.....

 

[David Biddulph]

The formula I gave works for me.
That formula was
=IF(B1+B2+B3,"Dear "&IF(B1,A1,"")&IF(B1*(B2+B3)," /
","")&IF(B2,A2,"")&IF(B2*B3," / ","")&IF(B3,A3,""),"")
but you may need to be careful if the newsgroup has split the formula at a
line wrap.
If it splits the lines at the " / " stage, there is a space, a slant, and
another space, between the quotes.

 

[David Biddulph]

I should, of course, have asked what you mean by "can't seem to get to
work".
What error message did you get? If no error message, what result did you
get for what input, and what result did you expect?

 

[Teethless mama]

=IF(SUM(B1:B3),"Dear ","")&IF(B1,"Jim"&IF(SUM(B1:B3)>1," /
",""),"")&IF(B2,"Bob"&IF(SUM(B2:B3)>1," / ",""),"")&IF(B3,"Mike","")

 

[Pete_UK]

This works for five names in A1:A5 and 1s or 0s in B1:B5:

=IF(SUM(B1:B5),"Dear ","")&IF(B1,A1&IF(SUM(B2:B5)," /
",""),"")&IF(B2,A2&IF(SUM(B3:B5)," /
",""),"")&IF(B3,A3&IF(SUM(B4:B5)," / ",""),"")&IF(B4,A4&IF(B5," /
",""),"")&IF(B5,A5,"")

Hope this helps.

Pete

 

[Sean]

Thanks David, I had a #value returned, but spotted where I went wrong

Thanks for the other posts too