IF Statement for various options

[HandyAndy]

Hi again everyone.
I have a situation of Sheet 1, has cells C29, E29, J29 and O29.
C29 is a drop down of office locations, say for example "Mumbai"
E29 is also a list of office locations, say for example "London"
J29 is also a list of office locations, say for example "New York"
O29 same again, office location is say "Houston".
In all cases above, in the drop down list, there is an option to select "None".

On Sheet 2, l want to show that if Sheet 1 C29 equals anything other than "None", return a value of 1 but if Sheet 1 E29 equals anything other than "None", return a value of 2.
And if Sheet 1 J29 equals anything other than "None", return a value of 3 and finally O29 equals anything other than "None" return a value of 4.
Essentially it is a count of which cells have a value other than "None".
It is not likely that C29 will show "Mumbai",E29 will show "None" and J29 will show "New York" and O29 will show "Houston". The expectation is the options will be selected in
sequence if that is relevant, such as C29 will show "Mumbai", E29 will show "London" and J29 and O29 will show "None".

Thanks in advance and l hope everyone has a great day!!
HandyAndy

 

[etaf]

does this work for you
=IF(C29<>"none",1,IF(E29<>"none",2,IF(J29<>"none",3,IF(O29<>"none",4,""))))

Not sure what version of Excel you have , a IFS() may work
=IFS(C29<>"none",1,E29<>"none",2,J29<>"none",3,O29<>"none",4)

 

[HandyAndy]

Thanks you, second one works best. I have Office 365 version.

 

[etaf]

you are welcome