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:

=COUNTIFS(C2:C20,”Man”,D2:D20,“>=50000”)

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:

=COUNTIFS(C2:C20,”Man”,D2:D20,“>=”&H2)

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:

1. The first on the string “Man”, on the “Gender” column
2. The second for an income value greater than 50,000, on the “Income” column
3. The third for an income value lower than 70,000, on the “Income” column

The formula is:

=COUNTIFS(C2:C20,”Man”,D2:D20,”>=50000″,D2:D20,”<=70000″)

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.

=COUNTIFS(C2:C20,”Woman”,E2:E20,”Single”)+COUNTIFS(C2:C20,”Woman”,E2:E20,”Divorced”)

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:

=COUNTIFS(B2:B20,”M*”)

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:

=COUNTIFS(A2:A20,”*Y*”)

There are now 5 people (Champney, Byerly, Gaudefroy, Fordyce, Freyer)

Related Articles

• SUMIF vs SUMIFS in Excel: The Trap of the Argument Order