Keeping a Running Count of Occurrences on a Spreadsheet

 Last reviewed: November 3, 1994
Article ID: Q76312
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

  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.

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.