Help in Rating based on criteria

[Pranav Pawar]

Hello,

I need to have rating done based on the criteria as mentioned below :

• All attributes under the standard reference format are complete with accurate and relevant data ( Column A to K ) - Rating 1
• Asset or Project details are missing. All other details are available ( Column J and K ) - Rating 2
• Asset or Project Name or Project details or Project Value are missing . All other details are available ( Column K, Column J, Column I ) - Rating 3
• Asset or Project Name or Project details and Project Value or Project duration or Decision date is missing ( Column K, Column J, Column I, column D and Column E ) - Rating 4

Please help

Many Thanks in advance.

 

[r1c1]

@Pranav Pawar Welcome to Chandoo.org forums and thanks for posting your question.

Here is a formula that seems to work. As we don't have lots of data in your file (just 1 row of test data), I am not 100% sure. Try it and let us know.

=IF(COUNTA(A2:K2)=11,"Rating 1", 
IF(AND(COUNTA(J2,K2)<2, COUNTA(A2:I2)=9),"Rating 2",
IF(AND(COUNTA(I2,J2,K2)<3, COUNTA(A2:H2)=8),"Rating 3", "Rating 4")))