How To Sum Using Logical Functions - An Example Of Using AND () And OR() In Excel

How To Sum Using Logical Functions - An Example Of Using AND () And OR() In Excel

MS Excel offers seven logical functions, today we will discuss how to use AND () when summing up values. The AND () function is a logical operator that tests if desired multiple conditions are meet. Thus conceptually if there are n conditions, all of them should be meet to return a TRUE in the formula.

 

(In contrast, if you are using OR() it implies that either of these could be meet to give a true.)

The syntax of formula is: =AND(logical1,[logical2],…)

 

The first argument is compulsory and must be provided to evaluate the formula, the second one is optional and one can add up to 256 such conditions. It is to be noted that if any of the conditions results in FALSE, the function will return a FALSE.

 

 

 

 

 

 

Example: Working out AND()

Let’s consider an example where an examiner wants to sum if the student has successfully completed two of the assigned tasks and his age is between 12 and 30 years.

In order to finish the task, the following formula should work:

=IF(AND(C3="Y",D3="Y",AND(E3>=12,E3<=30)),"Passed","Fail")

The inner most AND(E4>=12,E4<=30) is used to calculate the age bracket! If it falls between 1230, the function will return a TRUE. For the case of Anam, the age is 13 hence the formula will evaluate to AND(TRUE, TRUE) → TRUE and this is passed as a third argument to the outer AND() that check for the tasks if they are completed.

The outer AND() testifies if the tasks are completed by checking for “Y” in column C and D. We can see that for Anum, both tasks are completed hence a Y in cell C3 and D3. The AND() formula evaluates to this condition:                                      

=AND(C4="Y",D4="Y",TRUE)

=AND(TRUE,TRUE,TRUE)

=TRUE

This triggers the IF() formula and we have result “PASSED” in return.

 

Example: Working out OR()

Lets put a twist in the situation and assume that a teacher wants to find the number of student that has completed either of the assignment and is in age bracket of 1230. With this situation we are compelled to use and OR() condition within the AND() condition i.e. the student should fall between the age bracket plus either of the assignment be completed.

 

The formula that will work out here is:

IF(AND(OR(C3="Y",D3="Y"),AND(E3>=12,E3<=30)),"Passed","Fail")

The only part that has been added is OR(C3="Y",D3="Y") that checks if either of the condition is true. For the case of OR, if either of the condition returns TRUE, it will result in TRUE. Thus, from the previous formula, we can quickly reach the following conclusion: 

=IF(AND(OR(TRUE,TRUE),TRUE),"Passed","Fail")

=IF(AND(TRUE,TRUE),"Passed","Fail")

=IF(TRUE,"Passed","Fail")

=”Passed”

Thus concludes this short tutorial on logical functions. You can also try out NOR and XOR that works similarly.

 

Related Trainings

Good Biscuit

Good Biscuit

Understanding formulas with "Sales" operations.


Practice: VLOOKUP-II

Practice: VLOOKUP-II

Making more practice by using lookup functions.


Practice: VLOOKUP-III

Practice: VLOOKUP-III

Making more practice by using lookup functions.