### 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 (Champne**y**, B**y**erl**y**, Gaudefro**y**, Ford**y**ce, Fre**y**er)

Felix

02/10/2023 @ 17:51

I'm guessing :A9 is missing the $. Try this COUNTIFS(A$2:A$9,LEFT(A9,4)&"*")

Ranu

22/11/2021 @ 02:30

Hi,

Thanks for good explanation.

I am using Excel 2019 and have applied your formula pattern to:

=COUNTIFS(A$2:A9,LEFT(A9,4)&"*")

The result is "0" which should be not.

What is wrong?