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.

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.

### Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

### Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays