How to use Countif in Excel

When working with a large amount of data in Excel, you may find yourself needing to count the number of records which meet a specific criteria. This is when the Excel Countif function is going to save you a lot of time. Let’s take a look at how the Excel Countif function works and how to put it into practice.

If you are new to using formulas and functions, check out some of our earlier articles which will help you gain confidence and an understanding of how these work in Excel.

• Introduction to Functions in Microsoft Excel
• Formula Basics in Excel
• Introduction to the AutoFill tool in Excel

Countif Syntax

All functions in Excel have a syntax. This refers to the way in which the function is formatted – in other words, where you need to place a bracket or a comma. Below is the Excel Countif syntax.

=COUNTIF(range,criteria)

The “range” component within the syntax specifies where the data is located you wish to count. The “criteria” is the actual value you want to count.

Create a Countif in Excel

Let’s step through how you can create a countif function in Excel based on criteria you specify. In my example, I want to count how many sales we have in each state. I don’t want to have to sort by state, I want this information to be dynamic. This means that if any of the sales data changes, my Countif function will update itself accordingly.

1. Open Microsoft Excel.
2. If you have an existing worksheet you want to use this function in, go ahead and open it. Otherwise, create a sample worksheet similar to the one below:

3. Place your cursor in cell F8 which is where I want to countif for the state of ACT (I’m in Australia).
4. Go to the Formulas tab in Excel and click the More Functions button.
5. Choose the Statistical library and click COUNTIF.
6. Alternatively, you can click the Insert Function button and search for COUNTIF.
7. The Function Arguments box will now appear:

8. Place your cursor in the Range field and we need to select where the cells are we need to count. In this instance, I want to count by the state, so I need to highlight cells B5:B28.
9. Use the collapse arrow if you need to minimise the Function Arguments box in order to be able to highlight the cell range:

10. If you want to allow new records to be added later, you can include a “buffer zone”, by including some blank cells at the bottom of the column, you allow for your data to grow in the future.
11. In my example below I have highlighted down to row 35 which gives me 7 spare cells to use later which will be included in my Countif function:

12. Place your cursor in the Criteria field, you need to specify which criteria, or in this case, state of Australia, I want it to count. You can directly type the criteria into the field, or the better option is to cell reference the criteria. I have the criteria noted in cell E8 so I will cell reference it. Click the cell which contains your criteria:

13. Now click OK to see the result.
14. My result is now displayed as 4 sales for the state of ACT.

Copy the formula

In order to copy the formula, you will need to “lock” or apply absolute cell referencing to the cell range containing the states. This means that when we copy the function for the other states, the cell references will remain locked on, or “absolute” and will not change.

1. Click back into the cell containing our first Countif function.
2. Press F2 on the keyword to edit the cell or click into the Formula bar.
3. Place your cursor on the B5 cell reference and press F4 on the keyword which will automatically add the $ symbols into the cell reference, effectively locking it in place.
4. Repeat the process for the B35 cell reference which is the end of the Range area.
5. Your formula should look similar to the one below, with the “Range” area cells now locked in as absolute cell references:

6. Press Enter on the keyboard to complete the edit.
7. Now place your cursor on the bottom right corner of the cell where the small square is displayed (AutoFill tool):

8. Hold the left mouse button down and drag the formula down to the remaining cells so that the countif is copied for all states.
9. The Countif function is now displayed all my criteria:

10. If I want to test out the dynamic functionality, I can add a new record into the first available empty row, or I could change an existing record and my count values should adjust accordingly.

Extension Exercise

As an extension exercise, why not create a COUNTIF based on the sales persons name. E.g. Count how many sales David Grant made within the data set.

More than 1 count criteria

Let’s say we wanted to count how many sales we had in NSW and VIC at the same time? Check out the COUNTIFS function which allows you to specify multiple criteria.

I hope this helps you to create your own COUNTIF function, remember to leave any questions in the comment section below.