When working with large amounts of data in Excel, you can find yourself needing to count the number of records which meet specific criteria. This is when the Excel **Countif** function is going to come in very handy and 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.

## Countif Syntax

All functions in Excel have 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)**

## Create a Countif function in Excel

Let’s now step through how you can create a countif function in Excel based on criteria you set. In my example, I want to count how many sales we made 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.

- Open
**Microsoft Excel** - 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:

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

- 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** - Use the collapse arrow if you need to minimise the
**Function Arguments**box in order to be able to highlight the cell range

- 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
- 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

- 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 in cell**E8**so I will cell reference it. Click the cell which contains your criteria.

- Now click
**OK**to see the result - 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 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 now change.

- Click back into the cell containing our first
**Countif**function - Press
**F2**on the keyword to edit the cell or click into the**Formula bar** - 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 - Repeat the process for the
**B35**cell reference which is the end of the**Range**area - Your formula should look similar to the one below, with the “
**Range**” area cells now locked in as absolute cell references

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

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

- 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 counting totals should adjust accordingly.