Countif is an important function which is used to count number of cells which meet the given single condition or criteria. You can use Countif function to count number of cells which have any number, text or dates that match the given specific condition. But sometime we need to use wildcards for partial matching in the given range of cells in active worksheet. In that situation you must know how to use Countif function with wildcards in Excel
What is the Wildcard?
The wildcard are special characters. You can use them with different functions like Find, Replace, Match, Lookup for searching the given condition. There are two wildcards which is used for searching purpose ? and *. Both wildcards can be used in the criteria. A question mark wildcard matches any single character while an asterisk matches the sequence of characters.
Must Read: How to use COUNTIFS and SUMIF together in Excel
Countif Function
The Countif is a very popular and useful statistical function of MS Excel. This function is used to count numeric values from given range of cells as per match given condition. But first we have to know about the countif function in MS Excel. The syntax of this function is given below:
Syntax of Countif function: COUNTIF (RANGE, CRITERIA)
Range: It is a range of non-blank cells, which is used to count number of cells.
Criteria: You have to give and specific condition which you want to give as per your requirement to count the number of cells.
How to use Countif function with wildcards in Excel
For eg. If you have a Employee worksheet and want to calculate how many number of employees working on Full time or Part time status. In that situation you must have to use countif function in active worksheet. If you want to know how many employees are working for *Time status in that situation you must have to use asterisk wildcard (*). You must have to take few given steps to use countif function with wildcards in Excel sheet.
Step 1: Create the following worksheet in which you want to use countif function with wildcards in Excel.
Must Read: 3 Ways to find duplicate values on same worksheet – Excel
Step 2: If you want to know number of employees who are working for Full Time only. In that situation you have to apply following formula in cell H2 =COUNTIF($E$2:$E$16,I2) and press enter to get the result.
Step 3: if you want to know number of employees working for Part Time only. Now, you can apply the given formula in cell H3 =COUNTIF($E$2:$E$16,I3).
Step 4: To count number of all those employees who are working for *Time only. In that situation you have to use asterisk (*) wildcard with countif formula in cell H4 =COUNTIF(E2:E16,”*Time”). Now press enter to get the final result.
If you want to become an Excel expert then you must know different tips and tricks. You must also need deep knowledge of functions in Excel. I hope after reading this guide you can easily use countif formula with wildcards in Excel. If you have any suggestion regarding this guide then please write us in the comment box. Thanks to all.