Explanation
Imagine a company that uses a tiered commission structure for its sales team. Each salesperson is assigned a commission rate based on the total sales they have made. The commission tiers are structured like this:
- For sales less than $10,000, the commission rate is 10%.
- For sales from $10,000 to $20,000, the commission rate is 15%.
- For sales from $20,000 to $30,000, the commission rate is 20%.
- For sales over $30,000, the commission rate is 25%.
The goal is to calculate a commission for each person using the tiered structure above.
Note: the formulas below use decimal values like 0.1 for 10%, but you can use percentages like 10%, 15%, etc., directly inside the formulas if you prefer, and Excel will correctly evaluate the percentage values.
IF function
One way to solve this problem is with the IF function , as seen in the worksheet above, where the formula in cell D5 is:
=IF(C5<10000,0.1,IF(C5<=20000,0.15,IF(C5<=30000,0.2,0.25)))
This kind of formula is referred to as a " nested if " because we have several IF functions nested inside each other. Here’s how the formula works step by step:
=IF(C5<10000,0.1,...)
The first IF statement checks if the sales amount in C5 is less than $10,000. If so, it returns 0.1 as the commission rate. If not, it proceeds to the next IF statement.
IF(C5<=20000,0.15,...)
The second IF statement is nested inside the first. It checks if the sales amount is less than or equal to $20,000. If so, it returns 0.15 as the commission rate. If not, it moves on to the final IF statement:
IF(C5<=30000,0.2,0.25)
The third and final IF statement checks if the sales amount is less than or equal to $30,000. If so, it returns 0.2 as the commission rate. If not, it returns 0.25. Notice the last value is the value returned if none of the previous conditions are met , which only occurs if the sales amount is more than $30,000, the formula returns 0.25 as the commission rate.
For more examples of nested if formulas, see How to use the IF function and 19 tips for nested if formulas .
IFS function
Nested IFs can become complicated and hard to read as more conditions are added because additional conditions result in additional IF statements. The IFS function offers a more streamlined way to manage multiple conditions in Excel because it eliminates the need to nest multiple IF statements together. As a result, the syntax is much simpler:
=IFS(test1,result1,test2,result2,test2,result2,...)
For example, to solve this same problem with the IFS function, you can use the formula below in cell D5:
=IFS(C5<10000,0.1,C5<=20000,0.15,C5<=30000,0.2,C5>30000,0.25)
For more details, see: How to use the IFS function .
Calculating commission
Both formulas above calculate the correct commission rate for each sales amount in column B, but they don’t calculate the commission itself. This is done in a separate formula in cell E5:
=C5*D5
Alternatively, you can calculate the commission rate and the commission all in one formula like this:
=C5*IF(C5<10000,0.1,IF(C5<=20000,0.15,IF(C5<=30000,0.2,0.25)))
Or with the IFS function like this:
=C5*IFS(C5<10000,0.1,C5<=20000,0.15,C5<=30000,0.2,C5>30000,0.25)
Personally, I prefer to keep the calculations separate when possible because it makes the formulas simpler and easier to check.
Explanation
The goal is to take a specific action when a value begins with “x”, “y”, or “z”. As is often the case in Excel, there are multiple ways to approach this problem. The simplest way is to use the OR function with the LEFT function to create the required logical test. Another option is to use the COUNTIF function. Both approaches are explained below.
Note: this formula is more advanced because we need to test for “cell begins with”. For a more basic example of “cell equals this or that” see this page or this video .
OR + LEFT
The OR function returns TRUE if any argument is TRUE. For example, if cell A1 contains “apple” then:
=OR(A1="orange",A1="apple") // returns TRUE
=OR(A1="orange",A1="pear") // returns FALSE
For this problem, instead of an “equals to” test, we need a “begins with” test." For that, we can use the LEFT function , which is designed to extract text from the left side of a text string. For example:
=LEFT("apple",1) // returns "a"
=LEFT("apple",2) // returns "ap"
=LEFT("apple",3) // returns "app"
Putting these two functions together, we can test the value in cell A1 to see if it begins with “x”, “y”, or “z” like this:
=OR(LEFT(A1)="x",LEFT(A1)="y",LEFT(A1)="z")
If cell A1 contains “dog”, the formula above will return FALSE:
=OR(FALSE,FALSE,FALSE) // returns FALSE
If cell A1 contains “zebra”, the formula above will return TRUE:
=OR(FALSE,FALSE,TRUE) // returns TRUE
While this formula works well, it can be cumbersome to enter more values to test. One way to simplify the formula is to use an array constant with a single expression like this:
=IF(OR(LEFT(A1)={"x","y","z"}),"x","")
An array constant is a structure that holds multiple values. It works like a range in Excel, except the values in an array constant are hard coded. Because the result from LEFT is compared to three separate values in the array constant, the expression returns three separate results and the OR function evaluates these results as before. Putting this all altogether, we can use the formula above inside the IF function as the logical test. This is the approach used in the example shown, where the formula in cell D5 is:
=IF(OR(LEFT(B5,1)={"x","y","z"}),"x","")
In cell D5, the formula evaluates like this:
=IF(OR(LEFT(B5,1)={"x","y","z"}),"x","")
=IF(OR({FALSE,FALSE,TRUE}),"x","")
=IF(TRUE,"x","")
="x"
In cell D6, the formula evaluates like this:
=IF(OR(LEFT(B6,1)={"x","y","z"}),"x","")
=IF(OR({FALSE,FALSE,FALSE}),"x","")
=IF(FALSE,"x","")
=""
Notes: (1) The above formula uses an array constant to hold three hard-coded values to test. However, you can also use a normal range on the worksheet. Using a range makes it easy to provide more values to test, and to change these values at any time. (2) In this example, we are testing the first character only, so the num_chars argument in LEFT is 1. To test more than one character, adjust num_chars as needed.
COUNTIF function
Another way to solve this problem is with the COUNTIF function. COUNTIF counts cells in a range that meet a given condition (criteria). If no cells meet the criteria, COUNTIF returns zero. The generic syntax for COUNTIF is:
=COUNTIF(range,criteria)
For example, to count cells that equal “apple”, we can use COUNTIF like this:
=COUNTIF(range,"apple")
COUNTIF supports wildcards . To count cells that begin with “a”, we can use a formula like this:
=COUNTIF(range,"a*")
The asterisk (*) is a wildcard that means zero or more characters, so the meaning here is: begins with “a” . In the example shown, the formula in cell D5 is:
=IF(SUM(COUNTIF(B5,{"x*","y*","z*"})),"x","")
Working from the inside out, the core of this formula is COUNTIF, which is configured to count three separate values using wildcards and the array constant {“x*”,“y*”,“z*”} for criteria:
COUNTIF(B5,{"x*","y*","z*"})
The values in the criteria are supplied in an “array constant”, a hard-coded list of items with curly braces on either side. When COUNTIF receives the criteria in an array constant, it will return multiple counts, one count per item. For cell B5, COUNTIF returns the array {0,0,1}. The first count (0) is the count for cells that begin with “x”. The second count (0) is for cells that begin with “y”. The third count (1) is for cells that begin with “z”. Note that because we only give COUNTIF a one-cell range, it can return only one of two values: 1 or 0. Here are the result for the first 4 cells:
=COUNTIF(B5,{"x*","y*","z*"}) // returns {0,0,1}
=COUNTIF(B6,{"x*","y*","z*"}) // returns {0,0,0}
=COUNTIF(B7,{"x*","y*","z*"}) // returns {0,0,0}
=COUNTIF(B8,{"x*","y*","z*"}) // returns {1,0,0}
Because we are testing for 3 criteria with OR logic, we only care if any result is not zero. To check this, we add up all items using the SUM function. Excel will automatically evaluate any number as TRUE and zero (0) as FALSE. In cell B5, the formula evaluates like this:
=IF(SUM({0,0,1}),"x","")
=IF(1,"x","")
="x"
In other words, IF will return “x” whenever the result from SUM is not zero . However, if the result from SUM is zero , IF will return an empty string (""). In cell D6, the formula evaluates like this:
=IF(SUM({0,0,0}),"x","")
=IF(0,"x","")
=""
Note that this example uses 3 criteria: begins with x, y, or z. As with the LEFT formula above, you can add more values to test and the formula will continue to work.
TRUE or FALSE result
Both formulas above use the IF function to return “x” if a match is found and an empty string ("") if a match is not found. If you only need a TRUE or FALSE result the IF function is not required:
OR(LEFT(B5,1)={"x","y","z"}) // returns TRUE or FALSE
For the COUNTIF version, the logic needs to be adjusted a bit:
SUM(COUNTIF(B5,{"x*","y*","z*"}))>0 // returns TRUE or FALSE
Here we check if the result from SUM is greater than zero in order to force a TRUE or FALSE result.
Notes
- If you are testing many values, you can use a range instead of an array constant to provide values to check. In Excel 2019 and earlier, using a range will make the formula an array formula that must be entered with control + shift + enter. In the current version of Excel, no special handling is required.
- The COUNTIF function will accept ranges only for the range argument; you can’t feed COUNTIF an array that comes from another formula. This can be a problem when working with dynamic array formulas , where it is more common to pass arrays from one formula to another. The LEFT formula option does not have this limitation.
