Use a wildcard in Excel to make SUMIFS and COUNTIFS more flexible

What is the extra S in the function name?

The old functions SUMIF and COUNTIF let you add or count a range of numbers based on one criterion. For example, you could count all the members who have paid the annual fee in the table below. With SUMIFS and COUNTIFS you can have multiple criteria, e.g. count all the members who have a Silver membership and who have paid the annual fee.

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. But if you also want to be able to apply only one criterion without changing the setup, you can use a wildcard. Here’s how I’ve done it:

EasyExcel_28_1_SUMIFS and COUNTIFS with wildcard

The formula in F5 looks like this:


First, it goes through the cells B2 to B20 and checks how many of them that are equal to F2 (membership level). Then it checks C2 to C20 against F3 (paid Y/N). It will find 4 silver members in column B, and as it turns out, 3 of them have paid the fee.

EasyExcel_28_2_SUMIFS and COUNTIFS with wildcard

Now I want to check how many payments we have, regardless of membership level. The wildcard character in Excel is asterisk (*), and all you have to do is to type an asterisk in the criterion field, in this case F2 (see picture to the right)

How to make the report more user-friendly

EasyExcel_28_3_SUMIFS and COUNTIFS with wildcard

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, I suggest we 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

There are many other ways to use the wildcard feature too. Take a look at this article on how to use the wildcard to look for text within a text: Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP (external link, opens in new tab). It also explains how to use the question mark (?) and tilde (~) as wildcards.

Are you using Google Spreadsheets? Google Spreadsheets don’t have the functions SUMIFS and COUNTIFS, so instead you have to use the FILTER function combined with SUMIF and COUNTIF. Here’s a great article on about it: Making SUMIFS, COUNTIFS, & AVERAGEIFS functions in Google Spreadsheet (external link, opens in new tab).

Other easy tricks:

Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Did you enjoy this article? Get free email updates! Enter your email address here:

Delivered by FeedBurner

Clip to Evernote

Leave a Reply

Your email address will not be published. Required fields are marked *

× eight = 16

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>