# Wildcard with COUNTIFS or SUMIFS

### 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:

• 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:

=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)