[Quick Guide] Master The COUNTIF Function In Excel!

 

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

The COUNTIF a relatively simple function is designed to do just that and taught during our Excel Intermediate courses along with the COUNT function.

Want to learn how to use the COUNTIF function in Excel?

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.

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.

 

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 way you define criteria is very similar to those used in other Excel functions for example highlighting duplicate values or using IF functions.

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 the concatenate function is advisable. For example, you may define the range as follows:

 

Conclusion:

The COUNTIF function of Excel is one of the most commonly used functions of Excel.

It’s 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!