Make SUMIFS and COUNTIFS in Excel more flexible with a Wildcard

How to use a wildcard

In the example below I have used the COUNTIFS function to count the number of members who fulfill the two criteria in the report, and the SUMIFS function to calculate the total amount paid if both criteria are met.

=COUNTIFS(B2:B11,G2,D2:D11,G3)
=SUMIFS(C2:C11,B2:B11,G2,D2:D11,G3)

But what if you only want to see how many gold members there are, no matter if they have paid or not? Or you want to see the total payments, regardless of membership level? If you leave G2 or G3 empty, the formulas will return zero. The only way to do it without changing the whole setup is to use a wildcard.

The wildcard character in Excel is asterisk (*), and all you have to do is to type an asterisk in the criterion field(s). For example, if you replace “Gold” in G2 with an asterisk (*), the formulas will not consider the Membership criteria, and the results will be the totals of all members who have paid the fee.

Make your report more user-friendly

If someone else is going to use the report, chances are they don’t know how to use the asterisk as a wildcard. To make the report as user-friendly as possible, it might be a good idea to create drop-down lists in the criteria fields, with the asterisk as one of the choices.

How to create a drop-down menu: Create a drop-down list in Excel in 3 minutes

Wildcard inside a formula
You can use the wilcard inside a formula as well. For example, if you want to count the number of names in the list that contain the letter combination “wil”, you can ask COUNTIFS to look for “*wil*”. Of course, you don’t want to hardcode any variables in the formula, so a good way to do it is to refer to a cell where you can change the criterion:

=COUNTIFS(A2:A11,”*”&F19&”*”)

There are other wildcards as well, like “?” (placeholder) and “~” (modifier), but the asterisk wildcard works for most occations and will take you a long way.