Microsoft KB Archive/76312

{|
 * width="100%"|

Keeping a Running Count of Occurrences on a Spreadsheet

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0

-

SUMMARY
You can use a SUM-IF array formula to keep an accumulated count of occurrences of specific items in Microsoft Excel (for example, keeping Year-to-Date tallies).

MORE INFORMATION
The following example maintains a running count of name occurrences in the specified range:

Example
  Enter the following into a spreadsheet:

      A1:     Fred            B1:     {=SUM(IF($A$1:A1=A1,1,0))} A2:    Barney          B2: A3:    Wilma           B3: A4:    Betty           B4: A5:    Fred            B5: A6:    Fred            B6: A7:    Wilma           B7: A8:    Betty           B8: A9:    Betty           B9: A10:    Betty           B10: A11:    Wilma           B11: A12:    Fred            B12:

Enter the formula in cell B1 by pressing CTRL+SHIFT+ENTER to enter it as an array formula. Do not enter the brackets manually.  Select cells B1:B12 and choose Fill Down from the Edit menu. The combination of absolute and relative references will allow the formula to update properly.  The following values are returned:

      A1:     Fred            B1:     1 A2:    Barney          B2:     1 A3:    Wilma           B3:     1 A4:    Betty           B4:     1 A5:    Fred            B5:     2 A6:    Fred            B6:     3 A7:    Wilma           B7:     2 A8:    Betty           B8:     2 A9:    Betty           B9:     3 A10:    Betty           B10:    4 A11:    Wilma           B11:    3 A12:    Fred            B12:    4 