Using Logical Functions in Excel!

Logical functions are some of the most popular and useful in Excel. They can test values in other cells and perform actions dependent upon the result of the test. This helps us to automate tasks in our spreadsheets.

Logical functions are functions that return or work with logical values (TRUE, FALSE)


Hereby am listing major logical functions which is widely using in excel:

  • IF Statement / NESTED IF

  • AND / OR

  • SUMIF /COUNTIF

  • SUMIFS /COUNTIFS

Lets lookin to in detail one by one.....


IF Statement


The Excel IF Statement tests a given condition and returns one value for a TRUE result and another value for a FALSE result. The function can be used to evaluate text, values, and even errors. It is not limited to only checking if one thing is equal to another and returning a single result. We can also use mathematical operators and perform additional calculations, depending on our criteria. We can also nest multiple IF functions together to perform multiple comparisons.


IF formula:

=IF(logical_test, value_if_true, value_if_false)

The function uses the following arguments:

  1. Logical_test (required argument) – This is the condition to be tested and evaluated as either TRUE or FALSE.

  2. Value_if_true (optional argument) – The value that will be returned if the logical_test evaluates to TRUE.

  3. Value_if_false (optional argument) – The value that will be returned if the logical_test evaluates to FALSE.

When using the IF function to construct a test, we can use the following logical operators:

  • = (equal to)

  • > (greater than)

  • >= (greater than or equal to)

  • < (less than)

  • <= (less than or equal to)

  • <> (not equal to)

Let me try to explain you the function through an example with the help of a video:


AND / OR Function


The and()/or() functions are rather easy to use. They can be used to determine if a certain statement (s) are true or false, or if EVERYTHING in the given criteria is true or false.


=AND(element1[,element2,element3,…])


Let me try to explain you the function through an example with the help of a video:


Nested IF Function


Usually, nested IFs are used when you need to test more than one condition and return different results depending on those tests.


If you need to test for more than one condition, then take one of several actions, depending on the result of the tests, one option is to nest multiple IF statements together in one formula. You'll often hear this referred to as "nested IFs".


The idea of nesting comes from embedding or "nesting" one IF function inside another. In the example shown, we are using nested IF functions to assign grades based on a score.


SUMIF/COUNTIF


The COUNTIF and SUMIF function offer you to count or sum the cells conditionally on the basis of single condition and these formulas are compatible with all Excel versions.


=COUNTIF(criteria range, criteria)

=SUMIF(criteria range, criteria, sum range)


Let me try to explain you the function through an example with the help of a video:


SUMIFS/COUNTIFS


SUMIFS function in excel is similar to excel SUMIF function with just one minor tweak which actually is the specialty of SUMIFS and that is multiple criteria to validate against.


SUMIFS lets you compare multiple criteria and then perform the SUM of the respected field for those satisfying the supplied criteria.


SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)


COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.


COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)


Let me try to explain you the function through an example with the help of a video:






Thank you!


Mohammed Alfan

Keep Thriving. Keep Learning















38 views0 comments