Multiple IF

Multiple IF or Nested IF function in Excel

The multiple IF function example below can be applied in many situations. Sometimes we wish to calculate the bonus based not only on the sales performance but also on the designation of the consultant. In some cases we may wish to give an employee a conveyance allowance and we wish to do so based not only on his salary but also on his position in the company. Here we use multiple ‘if’, along with, maybe, ‘and’ and ‘or’ functions. Microsoft Excel provides many possibilities with the ‘if’ function to calculate and analyze data as demonstrated in the Excel training video. In case you find it difficult to follow the concept of the multiple ‘ifs’, please refer the  ‘if’ function Excel training video.


=IF(B2>=90,”A”, IF(B2>=70, “B”, IF(B2>=60,”C”,”Work Harder)))

In the training video we have assigned grades to students based on the marks they obtain. If a student has greater than or equal to 90% marks, he is assigned grade ‘A’ and so on.

Code example: ‘=if(and(A2=”senior consultant”, B2=200000),B2*10%, B2*5%)’  The above code is interpreted as follows: If the sales person is a senior consultant and his sales performance is $200000 then give him a bonus that is equal to 10% of his sales else the bonus should be calculated as 5% of the sales. A2, B2 are input data and the function is applied in, let’s say, cell C2.

Another code example with modification: ‘if(and(A2=”senior consultant”, B2>=200000), B2*10%, if(and(A2=”junior consultant”, B2>=200000), B2*5%, “Not Applicable”))’. The code is interpreted as: If the sales person is a senior consultant and sales is greater than or equal to $200000, he receives a bonus of 10% of his sales but if he is a junior consultant and his sales is greater than or equal to $200000, he receives a bonus of 5% and if the criteria are not true, then Excel displays a message “Not applicable”.  If you had more different designated sales people, you could continue with more ‘IF’ definitions!

Further reading

Excel/multiple if statement

One thought on “Multiple IF

  1. I have a large excel file. I would like to create a statement that would do the following: If Document Number in column A is unique (meaning not a duplicate) and the DIC in column B = A01 then copy and paste the entire line into a new worksheet. There could be hundreds of like possibilities. So the next doc number if meeting all the criteria would need to be extracted and placed under the first doc number that was extracted and so on. If you could help me with this it would be very very much appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.