Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Filtering Columns for Unique Values
Printing Multiple Worksheets on a Single Page
If you are working with a data table that has a limited number of categories by which you want a count, you can use the COUNTIF worksheet function to do your work. For instance, you may have a data table that has two columns. Column A could be names of customers and Column B could be names of sales representatives. There are only half a dozen sales representatives, but scores of customers.
In Column E, list the names of your sales reps, one rep per row. (If you have only a half dozen sales reps, you should have only six rows filled out.) Begin in Row 2, since E1 will probably be used for the column name, such as "Sales Rep." The sales rep names should be spelled exactly as they appear in the data table.
In Column F, beside the first sales rep, enter the following formula:
=COUNTIF($A$2:$B$200,"="&$E2)
Make sure you replace $A$2:$B$200 with the actual range of your original data table. (You could use a named range, if desired.)
Copy this formula (cell F2) into the other five rows of Column F (cells F3:F7), right beside each sales rep's name.
That's it! The information in Column F represents the number of customers for each sales rep.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2159) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Save Time! ExcelTips has been published weekly since late 1998. Past issues of ExcelTips are available in convenient ExcelTips archives. Have your own enhanced archive of ExcelTips at your fingertips, available to use at any time!