# Nested IF statement with multiple criteria

#### FM1

##### Board Regular
I have a spreadsheet where I'm trying to put together an IF statement which meets multiple criteria. Outline below:

Column B - Type - "Risk" or "Issue"
Column I - Probability - "1/2/3/4/5" (validated cell)
Column J - Impact - "1/2/3/4/5" (validated cell)
Column K - Rating - product of Column B and C (formula)
Column L - Severity - this is where I need the IF statement.

I need this cell to return the following where the Type (column B) = "Risk".

• If Rating >=20, then "Very High"
If Rating >=15 and <20, then High"
If Rating >=10 and <20, then "Medium"
If Rating >=6 and <10 "Low"
If Rating >= 1 and <6, then "Very Low"
If Rating = 0, then blank

The formula I have for the above, which works is: =IF(K4>=20,"Very High",IF(K4>=15,"High",IF(K4>=10,"Medium",IF(K4>=6,"Low",IF(K4>=1,"Very Low",IF(K4=0,""))))))

If however, Type = "Issue", then it needs to return the following:

• If Rating = 5, then "Very High"
If Rating = 4, then High"
If Rating = 3, then "Medium"
If Rating = 2, then "Low"
If Rating = 1, then "Very Low"
If Rating = 0, then blank

Last edited:

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### theBardd

##### Rules violation
How can two cells with values of 1...5 get a sum value of 20?

For Issue, what happens I the sum is greater than 5?

#### tyija1995

##### Well-known Member
Hey,

I assume here that cell B2 takes either "Risk" or "Issue" as its value, with the rating in cell K2 and formula placed into L2 this should work:

=IF(B2="Risk",IF(K2>=20,"Very High",IF(K2>=15,"High",IF(K2>=10,"Medium",IF(K2>=6,"Low",IF(K2>=1,"Very Low",""))))),IF(B2="Issue",IF(K2=5,"Very High",IF(K2=4,"High",IF(K2=3,"Medium",IF(K2=2,"Low",IF(K2=1,"Very Low","")))))))

• FM1

#### FM1

##### Board Regular
How can two cells with values of 1...5 get a sum value of 20?

For Issue, what happens I the sum is greater than 5?

Sorry, should have been a bit clearer. It's not a sum value, but a multiplication.

If it's an Issue then there is no sum or multiplication. I just need the single value, which corresponds to a rating, from column I.

Hey,

I assume here that cell B2 takes either "Risk" or "Issue" as its value, with the rating in cell K2 and formula placed into L2 this should work:

=IF(B2="Risk",IF(K2>=20,"Very High",IF(K2>=15,"High",IF(K2>=10,"Medium",IF(K2>=6,"Low",IF(K2>=1,"Very Low",""))))),IF(B2="Issue",IF(K2=5,"Very High",IF(K2=4,"High",IF(K2=3,"Medium",IF(K2=2,"Low",IF(K2=1,"Very Low","")))))))

Worked a treat. Thanks very much!

#### theBardd

##### Rules violation
Sorry, should have been a bit clearer. It's not a sum value, but a multiplication.

My bad, you did actually say that, I mis-read it.

Replies
3
Views
105
Replies
5
Views
260
Replies
5
Views
105
Replies
10
Views
139
Replies
17
Views
299

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

### Share this page ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back