[Quick Guide] Master The COUNTIF Function In Excel!

You often need to work out your data statistically by checking on particular portions of your data set that meet specific criteria.

The COUNTIF function is designed to help you with that.

Want to learn how to use the COUNTIF function in Excel? Continue reading below to master it!

Learn more about many more similar advanced features of Excel by joining our Excel course offerings.

 

What Is The COUNTIF Function?

The COUNTIF function of Excel is made to count in Excel the cells that meet a certain specified criterion. Take a look at its syntax below.

 

• Cell Range includes the range of cells where you want the value to be looked for
• Criteria define the parameter upon the basis of which you want the value to be counted

 

Taking it down to more straightforward terms, tell Excel:

Where are you looking for a value?

What are you looking for in that value?

 

Excel COUNTIF function is of great help to Excel users as it instantly allows you to filter out and count only those values that are unique to a certain criterion. That being said, you don’t need to look out for specific values in your data manually by scrolling your spreadsheet.

To use the COUNTIF function, you need to input the above-mentioned formula in the function bar. Fill in the arguments by defining the cell range where the values exist. Next, define the criteria based on which values are to be included in the selection.

Using the COUNTIF function, Excel returns the number of cells that contain the specified value. Learn more about this smart function by going through the article below.

For more on Excel functions, read this guide on the Transpose Function in Excel.

 

How Does It Work?

The COUNTIF function of Excel is a statistical function that counts the number of cells containing a specific value. Once you understand the science behind how this function works, you will find it helps you with all sorts of tasks.

To make the COUNTIF function work, you must define a range of cells. From this range of cells, if you want to count the number of values in Excel that have a specific characteristic, describe it as the criteria.

Excel would search out the values that meet your defined criteria and return the number of times such matter appear in the dataset.

It is often the case that you want to pick out values that meet multiple criteria. While the COUNTIF function cannot deal with such issues, the COUNTIFS function is specifically built to help for this case.

Syntax of the COUNTIFS function reads as follows:

 

• Criteria_range1 only represents the range of cells. This is the first compulsory range, and different ranges can be added if needed.
• Criteria1 represents the criteria against which the data is to be evaluated. This criterion can take the form of a number, a text string in quotation marks, a cell reference, or an expression.
• Criteria_range2 and Criteria2 are optional. You can apply the COUNTIFS function to multiple cell ranges against their associated criteria.

COUNTIFS function work when you have multiple ranges, and each range is to be evaluated individually against given criteria. The COUNTIF function can only work against a single range and a single criterion.

This is one of Excels best tools. Formulas and Functions are imperative to your success in Excel.

 

COUNTIF Function Use Cases

Given its versatility, the COUNTIF function of Excel has multiple uses ranging from uses for regular Excel users to businesses and even for many domestic purposes. Let’s look into some use cases below.

 

Use Case 1:

A business wants to declare the best employees for the year depending upon how many of them attended all the five annual corporate meetings. Here is how they can quickly set up a COUNTIF function for the said purpose.

 

 

Apply the COUNTIF function as follows:

 

B2:B8 represents the range from where the cells are to be counted. ‘5’ is the criteria against which the cells are to be selected and counted.

Here is how Excel has counted the number of employees who meet the defined criteria i.e. who have attended all the five corporate meetings.

 

 

Use Case 2:

In a home, while making budgets and analysing the trend of expenses, you may want to identify where did you overspend. Here is a quick example of how the COUNTIF Function may be of help.

Below is a domestic household expense summary.

 

 

From this data, we want to identify the number of months where the monthly expense exceeds the threshold of $5000 and those months where the expense is under $3000.

Here is how you can work out the COUNITF function to the said cause.

The result is as follows:

 

 

To highlight the months that have expenses under or above a certain threshold, use conditional formatting as explained here.

 

How to use COUNTIF Function – Simple Example

Bringing a COUNTIF function to action is a piece of cake only if you understand how its arguments work. Pulled together is a simple example of how a COUNTIF function works.

The screenshot below represents the source data upon which the COUNTIF function is to be applied. It shows a set of invoices out of which a few are claimed.

 

 

Step 1:

To identify the number of invoices that have been claimed, let’s put together a COUNTIF Formula as follows:

 

• • Here C2:C12 is the range of cells that contains the invoice status i.e. Claimed or Not Claimed
  • The criterion is set as “Claimed”. As this is a text string, it is put into quotation marks.

 

Step 2:

Hit enter to see Excel yield the following results.

 

 

Excel has counted and given the number of cells that contain the status ‘Claimed’ against the given invoices. This evidences that six invoices have been claimed up till now.

Pro Tip: To access the COUNTIF function from the Excel functions library, follow the route below.

 

 

How To Use The COUNTIF Function – Complex Example

The above example was an easy-peasy one. However, with larger data sets and more complex criteria, COUNTIF can get a little technical. Let’s see an example below.

Below is a dataset that represents the marks of different students.

 

 

Step 1:

To form statistics as to how many students stand at different grade levels, you may apply multiple COUNTIF Functions as follows.

 

And so on. The defined range i.e. B2 to B10 represents the marks of different students. The second argument represents the criteria i.e. the required marks.

 

Step 2:

Here is how Excel helps you bifurcate the result statistics into different sets.

 

 

Another case where the application of the COUNTIF function might prove a little complex is where the criterion is a little distorted. Take a look below.

 

 

The data above represents a list of customers who have subscribed to different packages offered by your company.

Taking a deep look into the data reveals that the same customer has subscribed for multiple packages. However, the name of the same customer is differently written. For instance, the customer name M. Alexander Smith can be seen written as Alexander, Alexandar, M. Alexander, and so on.

 

Now, to determine the number of packages subscribed by M. Alexander Smith, how should the criteria be defined? You can only input a specific spelling as a text string for Excel to identify and count. Here’s how you may deal with it.

 

Step 1:

In such situations, you may use the wildcard character i.e. the asterisk (*), to help the cause. Compose the COUNTIF formula as follows.

Step 2:

Hit enter. Doing so, Excel would only identify values that contain the word Alex irrespective of what is placed before or after. The results are as follows.

 

 

Excel has identified all the five values appearing in the dataset that contains the name ‘Alex’. The varying complete spellings, first name, and last name are ignored.

 

A quick comparison:

For a quick comparison, let’s do it the other way around. Defining the criteria ‘Alex’ as a simple text string would have yielded the following results.

 

 

Excel gives the result ‘0’ as it fails to identify any name that is equal to ‘Alex’.

Now that you are experienced with COUNTIF; you can use it for any number of needs. For example, given provided information, you can find trends in your data which were not obvious before. To help visualise this data, read this article on How To Make A Stacked Bar or Column Chart.

 

Troubleshooting with COUNTIF

Now we will go over some very common issues people have when using COUNTIF.

 

1. Put quotation marks around the Criteria

If the criteria you create is anything different than a simple cell reference, it must have quotation marks around it for Excel to recognise it. Otherwise, Excel may return an unexpected answer. See below and compare.

 

 

Add in quotation marks and see how the results change.

 

 

It is also to be noted how the criteria are not case sensitive. Excel would recognize ‘apple’ in the above example even if written as ‘Apple’ or ‘APPLE’.

In short, the protocols of defining a criterion are very similar to those when highlighting duplicate values.

 

2.   Wildcard Characters

When using wildcard characters like ‘?’ or ‘*’ in your criteria, you must note that Excel considers any single character equivalent to a wildcard character. For instance, the criteria ‘Apple?’ would match ‘AppleO’, ‘Apples’, or any other similar instance where the last character may vary.

 

3.   Erroneous Defined Range

If your data set and criteria consist of text values, be very cautious. Even the tiniest of mistakes in either of the two can cause Excel to return erroneous results. Double-check your data for any trailing or leading spaces, undue quotation marks, and other nonprinting characters.

Take a look below to see how this may cause a problem.

 

 

The given data set clearly has the name Ben listed four times. However, as two of the list items have spaces between the name, Excel hasn’t included them in the count.

 

4.   Long Strings

The COUNTIF function fails to function correctly when confronted with long text strings. If your dataset contains values, each of which contains 255 or more characters, the COUNTIF function might not recognise it and return inappropriate results.

For longer text strings, the usage of a concatenate function is advisable. For example, you may define the range as follows:

 

Learn more details about the Concatenate function here.

 

Conclusion:

The COUNTIF function of Excel is one of the most commonly used functions of Excel. It’s also used to create histogram charts in Excel, for example.

This is for the reason of its ease to use and common application to daily life.

Learning the COUNTIF function can help you with many of your easy and complex Excel jobs.

Try the above examples and practice to master!