XL: How to Use Microsoft Excel to Count All Entries in a List

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, version 5.0, 5.0a

SUMMARY

This article explains how to use the Consolidate command to create a list that contains one of each name in a list and a count of the number of times each name appears in that list. For example, if you have the following list of names

   A1: Albert
   A2: Bob
   A3: Dave
   A4: Albert
   A5: Bob
   A6: Ed

   C1: Albert    D1: 2
   C2: Bob       D2: 2
   C3: Dave      D3: 1
   C4: Ed        D4: 1

MORE INFORMATION

Note that the first column of the reference must be the names and there must be a column of numbers next to it. Any additional columns may give unexpected results.

To do create a list using Consolidate, use the following steps:

1. On a new worksheet, create a list of names and numbers:

         A1: Albert    B1: 2
         A2: Bob       B2: 3
         A3: Dave      B3: 2
         A4: Albert    B4: 1
         A5: Bob       B5: 2
         A6: Ed        B6: 1
   
   

2. Select cell C1, and then click Consolidate on the Data menu.

3. Select Count in the Function list.

4. Specify $A$1:$B$6 in the reference area, and under Use Labels In, select the Left Column check box. Click OK.

   C1: Albert    D1: 2
   C2: Bob       D2: 2
   C3: Dave      D3: 1
   C4: Ed        D4: 1

By specifying other functions, such as SUM, these results can be different. For example, you can calculate a subtotal for each of the names.

NOTE: The result cannot be dynamic; that is, if the data changes, you must follow steps 2-4 again.

REFERENCES

"Microsoft Excel User's Guide," version 5.0, Chapter 5, "Retrieving and Analyzing Data from Lists and Tables"