I love it when somebody teaches me a faster, better way to do something. In “Counting items in an Excel list,” I showed you my way of solving a common business problem—counting number of customers in each ZIP code.
My approach was to sort the list by the appropriate column and use Advanced Filter to copy a list of the unique entries to a different column. Then I used the =Countif function to count the number of times each unique entry appears in the original list.
Since we published that tip, a number of TechRepublic members posted comments or sent me e-mail suggesting that there’s a better way. That’s one of the best things about spreadsheet applications—there are often many ways to solve a given problem. Here’s the scoop.
The Subtotals feature will do
TechRepublic members called me on the carpet for not taking advantage of the Data menu’s Subtotals feature. To use this feature, open the Data menu and choose Subtotals. By default, the Subtotals dialog box uses the Sum function; just change that option to Count, as shown in FigureA. When you click OK, Excel will automatically generate subtotals for each ZIP code, as shown in FigureB.
| Figure A |
 |
| To find out how many customers reside in each ZIP code, use the Subtotals dialog box with the Count function. |
| Figure B |
 |
| Here’s how your spreadsheet data will look after the subtotals are generated. |
Where your customers aren’t
This report tells a business not only where its customers are, but also where they aren’t. When you scan the list of unique ZIP codes in the database, you can tell which ZIP codes are conspicuous by their absence.
My only complaint about using the Subtotals function is that the list is so long because it contains detail records. Fortunately, it’s easy to collapse that list—once you locate the right button. To collapse the detailed list of subtotals and display just the subtotals for each ZIP code, click the level 2 button, which is circled in red in FigureC.
| Figure C |
 |
| The Subtotals feature lets you collapse or expand your data as needed. |
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.
