Microsoft KB Archive/214079

= XL2000: Formula to Count the Number of Rows in Which an Item Appears =

Article ID: 214079

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214079





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( = ,ROW),

IF( = ,ROW))>0,1,0))

where  is the range that you want to search, and   is the number or text string that you want to find within each row. If you are searching for a text string, include quotation marks around both instances of.

NOTE: The formulas discussed in this article must be entered as array formulas by pressing CTRL+SHIFT+ENTER.



MORE INFORMATION
For example, to find out how many rows in which "XXX" appears 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.

Additional query words: count

Keywords: kbhowto kbinfo KB214079

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.