XL: Formula to Count Number of Rows That an Item Appears In

 Last reviewed: February 2, 1998
Article ID: Q100790

The information in this article applies to:
  • Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, you can use the FREQUENCY() function in a SUM(IF()) array formula to count the number of rows in which an item appears within a specified range.

To count how many rows in which a value appears in a specified range, use the following formula 

   =SUM(IF(FREQUENCY(IF(range=item,ROW(range)),
      IF(range=item,ROW(range)))>0,1,0))
where "range" is the range you want to search and "item" is the number or text string you want to find within each row. If you are searching for a text string, include quotation marks around both instances of "item."

NOTE: The formulas discussed in this article must be entered as array formulas by pressing CTRL+SHIFT+ENTER in Microsoft Excel for Windows or by pressing COMMAND+ENTER in Microsoft Excel for the Macintosh.

MORE INFORMATION

For example, to find out how many rows "XXX" appears in within the following range 

   A1: XXX         B1: XXX         C1: XXX
   A2: YYY         B2: XXX         C2: XXX
   A3: DDD         B3: DDD         C3: YYY
   A4: XXX         B4: YYY         C4: DDD
use the following array formula: 

   =SUM(IF(FREQUENCY(IF(A1:C4="XXX",ROW(A1:C4)),
    IF(A1:C4="XXX",ROW(A1:C4)))>0,1,0))
This formula returns the value 3, indicating that "XXX" is contained in three rows of the range.

REFERENCES

"Function Reference," version 4.0, pages 178-179, 364

Additional query words: 4.00 4.00a 5.00 5.00a 5.00c 7.00 7.00a 97 98 XL98
XL97 XL7 XL5 XL4 count
Keywords : xlformula
Version : WINDOWS:4.0,5.0,7.0,7.0a,97; MACINTOSH:4.0,5.0,98
Platform : MACINTOSH WINDOWS

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: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.