Microsoft KB Archive/138889

{| = XL: How to Use Microsoft Excel to Count All Entries in a List =
 * width="100%"|

Last reviewed: February 2, 1998

Article ID: Q138889 The information in this article applies to:
 * 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 you can use consolidate to display one of each name and the number of times it appears in the list: 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:

  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  Select cell C1, and then click Consolidate on the Data menu. Select Count in the Function list. Specify $A$1:$B$6 in the reference area, and under Use Labels In, select the Left Column check box. Click OK.

The result should be: C1: Albert   D1: 2 C2: Bob      D2: 2 C3: Dave     D3: 1 C4: Ed       D4: 1 The name Albert appears twice in the list, Bob twice, Dave once, and Ed once. Note that the result is displayed in the same sequence that Microsoft Excel finds the names in the source list. 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.