Nested IF statements for 3 conditions and multiple outcomes? or COUNTA?

[br1g1d]

Hi!

I have 3 conditions (A,B,C) with either no or text as input.

I'd like all possible combinations in the results column.


|____A____|___ B____|____C____|__getting__|___wanted__|

|____NO___|____NO___|____NO___|____n/a____|____n/a____| works!

|____3Z___|____NO___|____NO___|___A @ 3Z__|___A @ 3Z__| works!

|____NO___|____5A___|____NO___|___B @ 5A__|___B @ 5A__| works!

|____NO___|____NO___|____2B___|____n/a____|___C @ 2B__| no!

|____2Z___|____4A___|____2B___|___B @ 4A__|_A @ 2z, B @ 4A_| no! but almost!

|____NO___|____5R___|____7J___|____n/a____|_B @ 5R, C @ 7J_| no!

|____9H___|____6W___|____2S___|___B @ 6W__|_A @ 9H, B @ 6W, C @ 2S_| no!


The formula I'm using is:

=IF(A2="no","n/a",IF(B2="no",IF(C2="yes",A2&" & "&C2,A$1&" at "&A2),IF(C2="Yes",A2&","&B2&" & "&C2,B$1&" at "&B2)))

 

[SirJB7]

Hi, br1g1d!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you're not gonna ask for the commas, try this:

=ESPACIOS(SI(CONTAR.SI(A2:C2;"NO")=3;"N/A";SI(A2="NO";"";"A @ "&A2)&SI(B2="NO";"";" B @ "&B2)&SI(C2="NO";"";" C @ "&C2))) -----> in english: =TRIM(IF(COUNTIF(A2:C2,"NO")=3,"N/A",IF(A2="NO","","A @ "&A2)&IF(B2="NO",""," B @ "&B2)&IF(C2="NO",""," C @ "&C2)))


Regards!

 

[nazmul_muneer]

Br1g1d,

Think your data from A1:C7

Type the formula in E1 and copy down.


=TRIM(IF(COUNTIF(A1:C1,"NO")=3,"N/A",IF(A1<>"NO","A@"&A1,"")&" "&IF(B1<>"NO","B@"&B1,"")&" "&IF(C1<>"NO","C@"&C1,"")))


Regards,


Muneer

 

[Ajit Chauhan]

Please try below formula:


=IF(COUNTIF(A2:C2,"NO")=3,"n/a",IF(A2<>"NO",A$1&" @ "&A2,"")&IF(B2<>"NO",B$1&" @ "&B2,"")&IF(C2<>"NO",C$1&" @ "&C2,""))

 

[br1g1d]

Thank you SirJB7, Muneer & Ajit!


I can do without the commas in the result and searching the site for nested if statements didn't yield quite what I was looking for.


Both methods work well with the exception of when there is a text value in column "B" and "no" in columns A & C. The result so far is "A @ No B @ 5AC @ No"


I'm still working through trying to figure it out


I appreciated such great help!

 

[SirJB7]

Hi, br1g1d!

My posted formula works for every case, I think. Check this uploaded file:

https://dl.dropbox.com/u/60558749/Nested%20IF%20statements%20for%203%20conditions%20and%20multiple%20outcomes_%20or%20COUNTA_%20%28for%20br1g1d%20at%20chandoo.org%29.xlsx

Regards!

 

[br1g1d]

Thank you!