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.


5 things to do to improve your cybersecurity in 2018

5 Things to Do to Improve Your Cybersecurity in 2018

| Security | No Comments
2018 is right around the corner, and here are our top five recommendations for things you can do to improve your cybersecurity in the new year. Cybersecurity has been the...
Tips for cleaning out your inbox

Tips for Staying on Top of Your Inbox

| Email, Tech Tip | No Comments
How to Get the Junk Out of Your Inbox Are you drowning under all the email you get in your inbox every day? Is it tough to sift out the...
Office 365 migration benefits - security and mobility

More Reasons to Jump to the Cloud with Office 365: Security & Mobility

| Cloud Solutions, Office 365, Security | No Comments
Recently we tackled just two of the big benefits of moving from an on-premise traditional Microsoft Office with email setup to Office 365. Those benefits were cutting out expensive, confusing...