Keeping a Running Count of Occurrences on a Spreadsheet

• 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

1. 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.



2. 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.



3. 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 

REFERENCES

"Microsoft Excel User's Guide, Book 1," version 4.0, pages 134-136.

"Microsoft Excel User's Guide," version 3.0, pages 115-117.

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0

Keywords          : 
Version           : 
Platform          : 
Issue type        : 

Last Reviewed: March 22, 1999