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: