Formula to Count the Number of Rows in Which an Item Appears This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This 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: DDDuse 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
| Article Translations
|
| United States | Change | | | All Microsoft Sites |
Help and Support
Back to the top
|
