IF with AND function in one formula with example step by step - Basic Nested IF Excel IF SUM and AND function

Hi Friends,

As I've promised in my last article, today I'll show you in detail the nested IF functions with IF() function and with AND function to resolve a complicated problem by the easiest methods or tricks.

Today I'll prepare a mark sheet with few conditions given below to understand the nested IF() and AND() function combined. We'll use Nested IFs with AND() Function.

Conditions

      1.      Every student should have to get minimum of 45 marks in each subject.

      2.      Total marks should be greater than 120

      3.      Grade :

a)      "A" - Greater than 80%

b)      "B" - Greater than 60%

c)      "C" - Greater than 50%

d)      "No Grade" - For Failed Students.

Understanding IF Function in Microsoft Excel

As the image shows I have written four column headers like Total Marks, PASS/FAIL, % and Grade to make things easy to understand. Now, I'll describe each column header step by step.

Total Marks

In the Total Marks column I just used SUM() function to get the total number for each student. So, that's the easy part.

PASS or FAIL

To judge whether a student has "pass"ed all the subject we'll use nested IF function (IF within IF) and 'll create a nested formula using AND function. Because IF function can normally can take only one logical test as input.

Now to appear in each subject every student should have to get 45 marks in each subject and if one of these three subject's marks is below 45 then the student will not Pass.

The syntax of IF function is =IF(logical_test, value_if_true, value_if_false), therefore, we can write our IF formula in G3 cell (refer the above image) for this example as =IF(D3>45 and ... but what about next two subjects (?) cell address is E3 & F3. Now its time to use AND() function, as AND function can take upto 255 input as its parameter and the most important characteristic of this function is that if any one of the given conditions within AND function returns FALSE, it will return FALSE logical output meaning that if we write "FAIL" in the third parameter i.e., value_if_false of IF function then FAIL will be printed whenever the output of AND function is FALSE and if we write "PASS" in the second parameter of IF function i.e., value_if_true part and if the output of AND function is true then "PASS" will be printed. Easy??? Yes it is. So Lets' do it.

Now if I individually write AND function for this problem it will be like this... =AND(D3>=45,E3>=45,F3>=45).

If we press enter it will show us either TRUE (if marks of all the three subject is greater than or equal to 45) and FALSE (if marks of all the three subject is less than 45).

Now we'll just cover this function by IF function and additionally write "PASS" in the value_if_true part and "FAIL" in the value_if_false part like ...

 AND function => =AND(D3>=45,E3>=45,F3>=45)

Cover by IF  => =IF(AND(D3>=45,E3>=45,F3>=45),"PASS","FAIL")

How IF function Works

"One thing I would like to share to you is that you can remember IF function like this "IF this satisfied then THIS else THAT" and remember that THEN & ELSE is a comma (replace by comma). This, I learnt from my first computer teacher."

Now press enter and you are done, your formula will print PASS or FAIL depending upon the marks obtained.

IF AND function combination

Now you might be little surprised to see the G3>120 additionally in the AND formula, its nothing but the second condition that Total Marks should be greater than 120.

You just put a comma at the end of the AND function and type or select G3 and tell IF() that it should be greater than 120, that's' it, done.

 =AND(D3>45,E3>45,F3>45,G3>120)

If you drag this formula to the end of the data, it'll show you the Pass or Fail of each student.

% Percentage

I did it here very simply by dividing Total marks by 300 as 100 marks is the highest marks for each subject.

GRADE

Here we'll use nested IF function but before that lets understand the problem as understanding the problem will make our task easier.

The task has two parts, one based on the percentage achieved in three subject we have to put Grade and number two if any student is FAILED then there will be printed No Grade.

If we think logically then first of all we need to check the second condition because if any student has failed then no further checking is needed directly we can print "No Grade!!!".

So we'll begin with =IF(H3="FAIL", "No Grade", ...) here H3 is the cell GRADE for first student. Now if the student has no FAIL status then the IF function will move to value_if_false part and here is the trick, we'll again insert another IF function like =IF(H3="FAIL", "No Grade", IF()) and within this second IF function we'll write (I3>=80%,"A",) and like the above method we'll insert another IF function in the  value_if_false part of this new inserted IF() and after that another and close this formula by braces (opening and closing braces or brackets should be equal otherwise Excel will give you error) finally our formula will look like

=IF(H3="FAIL","No Grade",IF(I3>=80%,"A",IF(I3>=60%,"B",IF(I3>=50%,"C"))))

How to use IF with AND function in combination

This is how to use Nested IF and Nested function AND(). Below image will make you very clear about the topic discussed.

Nested IF function in Excel

You can watch video clicking in this here or below video frame.

I hope this part will make your understanding of IF function more clearer, if you like this blog please click on the follow button and keep commenting.

Thank you for reading...