Formula for mutually exclusive values

[detriez]

I have a sheet with 10 columns that I need to classify as follows.
Classifications are based on completeness of data.but I'm not sure that these will be mutually exclusive or how to write the formula.


If columns A-J are not blank, value = Gold

If columns A-G are not blank and either H, I or J are blank, value = Silver

If columns A-E are not blank and either F, G, H, I, J, are blank OR if A-E are not blank and E="US" F is not blank and either G, H, I, J are blank, value=Bronze

All the rest, value = None

 

[Special-K99]

Unclear.

What do you mean by columns A-J are not blank?

A1:J1000 say are not blank? The entire range, ie the entire column?
Or do you mean the cells on the same row e.g. A1:J1 are not all blank?

 

[tyija1995]

Hey,

Assuming you mean the entire column has at least 1 value in it then use this messy but workable formula (not sure about that last condition though with E="US" (?)

=IF(AND(COUNTA(A:A)>0,COUNTA(B:B)>0,COUNTA(C:C)>0,COUNTA(D:D)>0,COUNTA(E:E)>0,COUNTA(F:F)>0,COUNTA(G:G)>0,COUNTA(H:H)>0,COUNTA(I:I)>0,COUNTA(J:J)>0),"Gold",IF(AND(COUNTA(A:A)>0,COUNTA(B:B)>0,COUNTA(C:C)>0,COUNTA(D:D)>0,COUNTA(E:E)>0,COUNTA(F:F)>0,COUNTA(G:G)>0),"Silver",IF(AND(COUNTA(A:A)>0,COUNTA(B:B)>0,COUNTA(C:C)>0,COUNTA(D:D)>0,COUNTA(E:E)>0),"Bronze","None")))

 

[Special-K99]

Or if you mean on one row try

=IF(COUNTA(A1:J1)=10,"Gold",IF(AND(COUNTA(A1:G1)=7,COUNTA(H1:J1)<>3),"Silver",IF(OR(AND(COUNTA(A1:E1)=5,COUNTA(F1:J1)<>5),AND(COUNTA(A1:E1)<>5,E1="US",F1<>"",COUNTA(G1:J1)<>4)),"Bronze","OTHER")))

 

[tyija1995]

Just curious Special-K99,

Your 2nd condition "IF(AND(COUNTA(A1:G1)=7,COUNTA(H1:J1)<>3" when the first condition "IF(COUNTA(A1:J1)=10" surely the AND is not required here? Wouldn't it suffice to say IF(COUNTA(A1:G1)=7,"Silver" instead? As this condition is only read if the count of A1:J1 is not​ 10

 

[Special-K99]

@tyija1995: Oh yep, good spot! I didnt see that. Just going by the OPs description sequentially.

 

[detriez]

Sorry I wasnt clear.. I meant row.. A1:J1