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.

  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:

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

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

  1. 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
  2. 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

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

  1. Now click OK to see the result
  2. 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.

  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

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

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

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

Reference: https://www.thetraininglady.com/countif-excel/