Article ID: 100790 - Last Review: August 15, 2003 - Revision: 1.1 Formula to Count the Number of Rows in Which an Item AppearsThis article was previously published under Q100790 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 that 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 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
=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
| Article Translations
|

Back to the top
