nested if formula

[logisticsexcel]

Hi,

Hoping someone can help.

In the attached file i have worked out part of the result that I want.
Next i am trying to do is if there is Chelsea in column A only then show a result(Yes/No) in column O otherwise blank cell.
I tried a IF statement out side the existing IF but then excel is showing all result as blank even if there is Chelsea in Column A.

Thanks in advance

[AliGW]

It's because there is no match with Chelsea. Why? Because column A contains leading and trailing spaces. To get round it, try this:

=IF(TRIM(A2)<>"Chelsea","",IF(AND(COUNTIF(C2:N2,"X")=1,N2="x"),"Yes","No"))

[logisticsexcel]

Thanks a lot. I never thought about that.

[AliGW]

It's something that catches a lot of people out. If you ever suspect it might be happening, you can check that the length of the cell matches the number of characters you see using this:

=LEN(A1)

Ideally you should clean up the source data and get rid of those spaces. Spaces often appear when data is copied in from an external source.

[logisticsexcel]

Hi,

Thanks for your help. I am trying to get a different result for the same data.
1. Show a result in Column O only if there is Chelsea in Column A. Otherwise keep blank cell.
2. If there is Yes in Column B then show result Yes
3. If there is only one x in the range C to N and that x is in Column M then show result Yes
4. If there is more than one x in the range and even if there is an x in columnM then show result No

I spend a lot of time but unable to get it. Hope it is possible

Thanks Again