I hate it when I see users wasting time doing something manually that could be done with software. Recently, I visited a friend who was working on a printout that was obviously generated by a spreadsheet application. It was a list of customer names and addresses sorted by ZIP code, and my friend was manually counting how many customers were in each ZIP code.
Naturally, I had to stick my nose into the process and point out that there’s a much better way to get those numbers. Here’s the scoop.
Here’s how the tip works:
- Sort the list by the appropriate column.
- Use Advanced Filter to create a list of the unique entries in the appropriate column.
- Use the =Countif function to count the number of times each unique entry appears in the original list.
Sort, filter, and count
To demonstrate this technique, we’ll count the number of occurrences of each ZIP code in the sample data in FigureA. Note that when you’re doing this exercise “for real,” you don’t need first name, last name, address, city, and state columns. Just copy the entire ZIP code column and paste it into a new sheet.
|We’ll count the number of times each ZIP code appears in our sample data.|
Begin by sorting the list of entries. To do so, you can simply click on any of the cells in column A and click the Sort Ascending button in the Standard toolbar.
Next, open the Data menu, select Filter, and then Advanced Filter. (If you don’t have a column label, you’ll need to select the range first. If you click OK without a column label present, Excel will treat the first entry as the label.)
When the Advanced Filter dialog box appears:
- Click the radio button for the option labeled “Copy to another location” in the Action section.
- Click the check box for the “Unique records only” option.
- Type B1 in the Copy To field (or click the tool at the end of that field and navigate to B1).
When that dialog box looks like the one shown in FigureB, you’re ready to click the OK button and execute the Advanced Filter action. When you do, Excel will place the list of unique entries in your worksheet starting in cell B1, as shown in FigureC.
|We’ll use the Advanced Filter dialog box to generate a list of the unique entries in our source data.|
|We’ll use the unique list of entries to generate our “counts by ZIP code” values.|
At this point, all that’s left is to compose a formula that counts the number of times the entries in column B appear in column A. In cell C2, enter the formula
and press [Enter]. Then copy that formula into cells C3 through C9. When you do, Excel will display the number of times each unique entry appears in the original list. (The dollar signs make the row references absolute so they don't adjust when you copy the formula. You could also assign a name to the range A2:A21 and use that range name in the formula.)
In the Countif function’s first argument, you specify the range of the source data. Countif’s second argument is the literal value or reference to the cell containing the entry whose occurrences you want to count. So the function says, “Count the number of times the second argument appears in the range named in the first argument.”
FigureD shows our sample sheet after we copied that formula. Notice the bold 20 in cell C10? That’s where we inserted a Sum function to make sure that the number of counted entries adds up to the number of original entries. (Since we had 20 entries in our “source” data in column A, we’d expect the total number of unique entries counted to be same.)
|These results tell us how many customers reside in each ZIP code.|
If you’d like to comment on this tip or suggest a different approach, please post your remarks below or follow this link to send us a note.