Wildcard with COUNTIFS or SUMIFS

Greater than / Less than

You can create complex conditions with the COUNTIFS function.

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 understand.

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)

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.