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.
- Type “=COUNTIF(” (without the quotation marks).
- 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.)
- Type a comma “,” (without the quotation marks).
- 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.”.
- 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.