Advanced Excel Formulas =COUNTIF(range, criteria)

Have you every had the need to count a specific value in a set of data? Many of us who use excel have and in this article I'll walk you through how to count values using a COUNTIF statement and how to incorporate wildcards into your search.

The Basics: The COUNTIF formula requires two items:

• Range: the set of data you would like search in
• Criteria: What you are searching for. Note: If searching for text use quotations.

In the below example we have a set of name and we want to know how many times the name John appears. So we are looking in the set of data from A1 to A10 (A1:A10) and for the name John. Therefore our formula will be =COUNTIF(A1:A10,"John"). Notice that I put John in quotations because its a specific text value. If searching for a number you would not need the quotations. The below example returns a result of 3.

Take it to the next level using a wildcard: Wildcards are special symbols used to search for partial data in Excel. Its very common to combine the wildcard and this formula. The two most commonly used wildcards are:

• $: the dollar symbol represents any single character.
• *: the asterisk symbol represents an unlimited number of characters in the direction after the asterisk.

Here is an example of a combining the Wildcard into your COUNTIF formula. Notice in the picture below people now have last names in the set of data. If I want to still find out the number of people with the first name John I need to modify my formula to search for the word John and anything after. My formula would now be =COUNTIF(A1:A10,"John*"). The asterisk after the word Johns tells excel to count anything that starts with John regardless of text that comes after. The example again returns a result of 3. The wildcards can be used at the beginning, middle ($ only), or end of the text you are searching for depending on the way you would like Excel to search.

I hope you found this article helpful and happy Excel counting!