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:


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


COUNTIFS function with a criteria lower than

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)

Wildcard characters with a COUNTIFS function

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)

Related posts

Permanent link to this article: https://www.excel-exercise.com/wildcard-with-countifs-or-sumifs/

Leave a Reply

Your email address will not be published.