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.

Microsoft Outlook Connectivity Changes for Office 365

| Notifications | No Comments
On October 31st, Microsoft will be changing the way that Outlook connects to Exchange Online. If you are an Office 365 customer, you may be impacted by this change if...
Word document tips

Five Tips for Word Documents

| Tech Tip | No Comments
Do you spend a lot of time in Microsoft Word, working on projects, papers, proposals, and more? Here’s a quick review of five top tips for Word documents. #1 The...
Iowa Technology and Education Connection Conference

Meet up with OXEN Technology at the ITEC Fall Conference, Oct 16-17

| Education, Events | No Comments
OXEN Technology will be at the Iowa Technology and Education Connection (ITEC) Fall Conference on October 16-17. Stop by and see us at booth #201 and participate in our drawing!...