Multiple Nested AND / IF Statements

Do you have a spreadsheet that you want to label certain criteria if they have two conditions? In order to do this you can combine multiple IF with AND statements in Excel.


Below I have an example for a T-Shirt company that has an overabundance of certain t-shirts.


We want to apply discounts to orders that contain blue and purple quantities;

  • For any purple t-shirt orders that have a quantity equal to or over 100, a 1.5% discount will be applied.

  • For any purple t-shirt orders under 100, a 1.4% discount will be applied.

  • For any blue t-shirt orders equal to or over 100, a 1.3% discount will be applied.

  • For any blue t-shirt orders under 100, a 1.2% discount will be applied.

  • All other orders will receive a 1.1% discount.

For the above spreadsheet we want to create a nested AND/IF formula to apply the criteria to cell C2.


The formula will be as follows;


=

IF(AND(A2="purple",B2>=100),1.5,

IF(AND(A2="purple",B2<100),1.4,

IF(AND(A2="blue",B2>=100),1.3,

IF(AND(A2="blue",B2<100),1.2,

1.1))))


The breakout of this formula is as follows;


IF(AND(A2="purple",B2>=100),1.5,

If cell A2 contains the word "purple" and B2 is greater or equal to 100, enter a 1.5 discount.


IF(AND(A2="purple",B2<100),1.4,

If cell A2 contains the word "purple" and B2 is less than 100, enter a 1.4 discount.


IF(AND(A2="blue",B2>=100),1.3,

If cell A2 contains the word "blue" and B2 is greater or equal to 100, enter a 1.3 discount.


IF(AND(A2="blue",B2<100),1.2,

If cell A2 contains the word "blue" and B2 is less than 100, enter a 1.2 discount.


1.1))))

For all other orders, enter a 1.1 discount.


Copy and paste this formula into cell C2. Then paste it down to the other cells in the C column.


Your results will appear as below;






10 views0 comments