Greater than / Less than
You can create complex criteria with wildcard characters in your COUNTIFS functions.
You can enhance your criteria by adding a logical operator. In other words, you can select all the rows where one criterion is greater than a value.
For example, if we want to determine the number of men with incomes greater than or equal to 50,000, the formula is:
The logical operator must be written between double quotes.
The & symbol is needed to link the logical operator and the cell reference. Without this symbol, the criterion can't be understood.
If you use the cell reference, your formula is:
Select a data range
If you want to return the number of men with an income between 50,000 and 70,000, you must write your function with 3 criteria:
- The first on the string "Man", on the "Gender" column
- The second for an income value greater than 50,000, on the "Income" column
- The third for an income value lower than 70,000, on the "Income" column
The formula is:
How to do an OR with COUNTIFS
To count the number of rows containing single or divorced women, you have no choice but to create 2 formulas. One is for single women, and the second for divorced women.
Wildcard Search: Selection on part of the criteria
With a computer, you can perform a search on part of a text string using a wildcard, ? or *.
- ? takes the place of a single character. For example, searching for "T?m" could return "Tim" or "Tom"
- * takes the place of any number of characters. For example, searching for "L*a" could return "Lana" or "Loretta" or "Luka", among others.
Most of the time, you will use *. The ? is not really useful.
If you want to find all the people with a firstname starting with M, you can use the following formula:
There are 3 people in this list (Michael, Marie and Mathew)
Now if you want to find all the people with a Y in the name, you can use this formula:
There are now 5 people (Champney, Byerly, Gaudefroy, Fordyce, Freyer)