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:
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.
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
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 SpreadsheetPro.net about it: Making SUMIFS, COUNTIFS, & AVERAGEIFS functions in Google Spreadsheet (external link, opens in new tab).
Other easy tricks:
- Create a drop-down list in Excel in 3 minutes
- Create a dynamic drop-down menu in Excel in 4 easy steps
- Create a search field in Excel in 5 minutes
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.