Counting in Excel with COUNTIF

By May 29, 2017Tech Tip
Using COUNTIF in Excel

Maybe you have a list in Excel and you want to know the number of times repeating items appear. Say you have a list of contacts with their company names, and you want to know how many times a company appears (which is the number of contacts from that company on your list).

There’s an Excel formula for that, called COUNTIF!

Create a Legend

First, you need a list of unique company names only to create a “legend”. You can get this by copying the Company column and pasting it into another column. Then select the new column and go to Data > Remove Duplicates.

In our example, this shows that there are 4 unique company names.

Find the First Count with COUNTIF

Then, to use COUNTIF, select the cell to the right of the first item in your legend.

  1. Type “=COUNTIF(” (without the quotation marks).
  2. Select the range, which is the full list of items that you want to “search and count”. The range in our example case is all the company names in your original list. (Select the first item, then drag your cursor down to the bottom and select the last item.)
  3. Type a comma “,” (without the quotation marks).
  4. Then, select the criteria. The criteria is the item that you want to search for and count instances of. Select the first item on your legend. In our example, this is “Miller & Co.”.
  5. Finally, type a close parenthesis “)” to close the formula, and hit Enter.

In our example, this first value is “3” because “Miller & Co.” appears three times in the list.

To count the remaining items, you need to do two things.

Change Range Values to Absolute

First, select the cell you typed the COUNTIF formula in.

In the Formula bar at the top, highlight the range (which is CELL:CELL).

Then, hit F4 on your keyboard. CELL:CELL will change to $C$C:$C$C.

Hitting F4 cycles through combinations of absolute and relative cell references. “Absolute” is denoted with $ before the both the column letter and row number. By default, cell selections are relative. By making them absolute, you prevent the cell selection from changing.

Copy the Formula to Remaining Cells

Next, select the cell with your COUNTIF formula again.

In the bottom-right corner of the selected cell, there is a tiny square. Hover your cursor over this square and the cursor will become a + sign. Click, and then drag your cursor down to the cells beneath.

By dragging, you propagate your COUNTIF formula to all the cells next to items in your legend. At the last cell, release your cursor.

You should see count numbers next to each item in your legend now.

Because you made your COUNTIF range cell selection absolute, dragging the formula down maintains the same data selection to “count from”. Because you left the criteria selection (of an item in your legend) relative rather than absolute, dragging down the formula to more cells also changes the criteria selection. This means the criteria automatically updates to the next item in your legend.

And that’s it! Now you have a legend that shows counts of each unique item from your original list. The COUNTIF formula accepts a criteria and then searches for and counts the number of times that criteria appears in the “range” list.

Changing desktop background image

Windows 10 Tip: Changing Desktop Background and Lock Screen Pictures

| Tech Tip | No Comments
Want to customize your Windows 10 computer? Here’s how to customize your desktop background and lock screen background with images of your choosing. You can change your desktop background image...

Windows 10 Tip: Modify sleep settings

| Tech Tip | No Comments
Is your Windows 10 computer going to sleep and showing a black screen too quickly? It can be frustrating if it goes to sleep and locks after only 10 or...
Using COUNTIF in Excel

Counting in Excel with COUNTIF

| Tech Tip | No Comments
Maybe you have a list in Excel and you want to know the number of times repeating items appear. Say you have a list of contacts with their company names,...