Excel Conditional Functions

IF function

The most important conditional function is IF, which takes the form

IF(logical-test, value-if-true, value-if–false)

When the logical test evaluates to TRUE then the value of the cell containing the IF function is the value of the 2^nd argument, but when the logical test evaluates to FALSE then the value of the cell containing the IF function is the value of the 3^rd argument.

The logical test can be built up from the logical operators and comparison operators. For example

=IF(A5 > G6, …)
=IF(NOT(A7 = B8 + 7) AND (SUM(E2:E7) >= 0), …)

In addition, some special logical functions such as ISERROR, ISBLANK, ISODD, ISEVEN and ISNUMBER can be used. E.g. =IF(ISEVEN(A5),0,1) returns the value 0 if cell A5 contains an even number (or evaluates to an even numerical value) and returns the value 1 if cell A5 contains an odd number (or when it evaluates to a value that is not even).

While formulas such as =MAX(R1), =AVERAGE(R1), etc. ignore any blanks or alphanumeric values that aren’t numeric, they return an error value if R1 contains an error value such as #NUM or #DIV/0!. This limitation can often be overcome by using the following approach:

=AVERAGE(IF(ISERROR(R1),””,R1)

This array formula returns the mean of all the cells in R1 ignoring any cells that contain an error value. Since this is an array formula, you must press Ctrl-Shft-Enter (although you can simply press the Enter key if using Excel 365).

IF statements can also be nested. E.g. the formula

IF(A3>8,”GOOD”,IF(A3<0,”NEG”,”BAD”))

returns GOOD if the value in cell A3 is larger than 8, it returns NEG if this value is negative and it returns BAD if A3 has a value between 0 and 8 inclusive.

IFERROR function

This function takes the form IFERROR(value, value-if-error). The IFERROR formula takes the value of the first argument, except when this argument evaluates to an error value, in which case the IFERROR formula takes the value of the second argument.

E.g. The formula =IFERROR(A1/B1,-1) will take the value of the formula =A1/B1 provided B1 doesn’t have the value zero (in which case =A1/B1 would evaluate to #DIV/0!). If B1 does have the value zero, then the formula takes the value of the second argument, namely -1.

IFS function

As described above, you can nest IF statements. The problem with this is that the nesting (and especially the placement of parentheses) can become complicated and confusing. Excel 2019 introduced a new IFS function that simplifies such formulas and takes the form

IFS(logical-test1, value-if-true1, logical-test2, value-if-true2, …)

Thus IF(A3>8,”GOOD”,IF(A3<0,”NEG”,”BAD”)) can be expressed as

=IFS(A3>8,”GOOD”,A3<0,”NEG”,TRUE,”BAD”)

Note that this IFS formula contains three logical tests, the third of which is TRUE.

SWITCH function

Excel 2019 also introduced a new conditional function similar to IFS, which takes one of the following forms:

SWITCH(expression, value1, result1, value2, result2, …)

SWITCH(expression, value1, result1, value2, result2, …, result-default)

These are equivalent to

IFS(expression=value1, result1, expression=value2, result2, …, TRUE, #N/A!)

IFS(expression=value1, result1, expression=value2, result2, …, TRUE, result-default)

SUMIF function

This function takes the form SUMIF(R1, criteria, R2) where R2 contains the range of potential values to be summed and R1 is a range of the same shape and size containing values to be matched against the criteria. For each value in R1 that meets the criteria, the corresponding value in R2 is used in the sum. Where the values in R1 are numeric, criteria can take the form of a constant such as 34 or B5, or a logical expression (in quotes) of form “>34”, “<>34”, “<=34”or “>”&B5.

Note that you need to use the ampersand (i.e. the concatenation operator) when creating logical expressions that refer to a cell’s value. Thus the “<=”&B5 criteria is met for all cells in R1 which are less than or equal to the value in cell B5. The criteria B5 is equivalent to “=”&B5.

Figure 1 provides some examples of the use of the SUMIF function. E.g. if the value in cell A9 is changed to Divorced then the value in cell G7 will automatically change to 46.

Figure 1 – Examples of COUNTIF and SUMIF functions

COUNTIF function

This function takes the syntax COUNTIF(R1, criteria). The value of this function is the number of elements in range R1 that meets the criteria. While for SUMIF, R1 and R2 are usually one-dimensional (i.e. row or column vectors), for COUNTIF, R1 can take any m ×  n shape.

Where the values in array R1 are alphanumeric, the criteria argument takes forms such as “Male” or B5. Wildcards can also be used: a question mark (?) matches one character and an asterisk (*) matches any sequence of characters; e.g. “*ite” selects all values in R1 which end in the letters “ite”. To match an actual question mark or asterisk you need to proceed this character by a tilde (~); i.e. ~? and ~*.

Selection is case-insensitive, i.e. “Male” is equivalent to “MALE” or “mALe”. You can also use criteria such as “>=”&”M” to select all values in R1 which start with the letter M or higher.

Examples are shown in Figures 1 and 2.

Figure 2 – Additional examples of COUNTIF

SUMIFS and COUNTIFS functions

These functions, introduced in Excel 2019, are similar to the SUMIF and COUNTIF functions. The difference is that multiple criteria can be applied. The syntax of these functions is as follows where all the ranges have the same shape and size:

SUMIFS(R0, R1, criteria1, R2, criteria2,…)
COUNTIFS(R1, criteria1, R2, criteria2,…)

Although only two criteria are indicated above, these formulas can use as many criteria as you like (up to 127). The value of SUMIFS is the sum of all the elements in array R0 that meet all the criteria. An element in R0 meets all the criteria provided the corresponding element in R1 meets criteria1, and the corresponding element in R2 meets criteria2, etc.

The value of COUNTIFS is a count of the number of elements that meet all the criteria.

Note that SUMIF(R1, criteria, R2) is equivalent to SUMIFS(R2, R1, criteria).

AVERAGEIF and AVERAGEIFS functions

These functions, introduced in Excel 2019, use the same syntax as SUMIF and SUMIFS and calculate the average values of the elements that satisfy the listed criteria.

MINIFS and MAXIFS functions

These functions, introduced in Excel 2019, use the same syntax as SUMIFS and calculate the minimum and maximum values of the elements meeting the listed criteria.

Reference

Microsoft (2021) Excel functions
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm19