How Many Times Does Each Name Appear in a List?

Written by Allen Wyatt (last updated March 12, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


1

Richard used the UNIQUE function on a table of around 900 rows of different firms. The function identified the 600 or so unique firm names. He wonders if there is a way to figure out how many times each of the unique firms appears in the table, perhaps by combining UNIQUE with a different function.

There are actually a couple of different ways you can derive the information you want. Before providing solutions, however, it is best to lay out some parameters. Let's say that your firm names are in column A, with a column header in cell A1. This means your data is in the range A2:A901, for a total of 900 rows. Your unique list (using the UNIQUE function) is placed in cell C2, as follows:

=UNIQUE(A2:A901)

This provides you with 600 unique firm names, spilled into the range C2:C601. You could, if desired, sort the list of unique names by simply wrapping the formula in cell C2:

=SORT(UNIQUE(A2:A901))

You still end up with the 600 unique names, but they are now in alphabetical order. You can determine how many times each of the unique names appears in your original list by placing a formula into cell D2, right next to your UNIQUE formula:

=COUNTIF($A$2:A$901,C2)

Copy this down to the rest of the rows (D3:D601), and you'll have your counts.

It is obvious that Richard is using the version of Excel provided with Microsoft 365, otherwise he wouldn't be able to use the UNIQUE function. Because he is using this version, he could modify these last steps just a bit. With nothing in column D, add this variation of the formula into cell D2:

=COUNTIF($A$2:A$901,C2#)

Notice the addition of the hash mark at the end. Making this single-character change causes the results of the formula to spill down, as many cells as necessary, to provide results for the cells in column C. In other words, no copying of the formula to D3:D601 is necessary, and Richard has the counts he wants.

What if you are not using a newer version of Excel that supports the spillable functions like UNIQUE? If so, the traditional way of getting the unique values and counts is to use Excel's advanced filtering capabilities. Here are a couple of past tips I've written that describe how to use this capability:

https://tips.net/T7562
https://tips.net/T8732

Use the advanced filtering to pull the unique firm names (instead of using the UNIQUE function), and then you can use the COUNTIF formula (without the hash mark) to derive the number of instances that each unique firm name appears in your original list.

Finally, regardless of the version of Excel you are using, you could get your unique firm names and counts by creating a very simple PivotTable.

  1. Select a cell within your data table.
  2. Make sure the Insert tab of the ribbon is displayed.
  3. In the Tables group, click the PivotTable tool.
  4. Excel displays the Create PivotTable dialog box. (See Figure 1.)
  5. Figure 1. The Create PivotTable dialog box.

  6. In the Range box, make sure your entire data table is selected, then click on OK. Excel creates an empty PivotTable worksheet.
  7. Drag the Name field from the field list to the Rows area.
  8. Drag the Name field from the field list to the Values area.

That's it; your PivotTable is complete. Because you placed the Name field in both the Rows and Values areas, you end up with a sorted list of unique firm names and a count of how many times each name appears in your original data.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12856) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using the Spike to Edit

Most Word users are proficient in cutting and pasting text using the Clipboard. One of the lesser-known editing tools, ...

Discover More

Unique Name Entry, Take Two

If you need to make sure that a column contains only unique text values, you can use data validation for the task. This ...

Discover More

Conditional Formatting for Errant Phone Numbers

Conditional formatting can be used to draw attention to all sorts of data based upon the criteria you specify. Here's how ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Deleting Duplicate Text Values

Got a list of data from which you want to delete duplicates? There are a couple of techniques you can use to get rid of ...

Discover More

Changing the Reference in a Named Range

Define a named range today and you may want to change the definition at some future point. It's rather easy to do, as ...

Discover More

Using a Numeric Portion of a Cell in a Formula

If you have a mixture of numbers and letters in a cell, you may be looking for a way to access and use the numeric ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 - 3?

2022-03-14 04:36:41

Steve J

A single cell formula could be ;

with all of the names being in a table named tblEmployees & a column named Name

=SORT(UNIQUE(tblEmployees[Name])&" - "&COUNTIFS(tblEmployees[Name],UNIQUE(tblEmployees[Name])))

which results in a sorted list like;
Adam - 1
Adrian - 2
Alex - 1
Billy - 3
Charlie - 1


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.