Microsoft KB Archive/100790
Article ID: 100790
Article Last Modified on 8/15/2003
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 98 for Macintosh
This article was previously published under Q100790
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
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.
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:
This formula returns the value 3, indicating that "XXX" is contained in three rows of the range.
"Function Reference," version 4.0, pages 178-179, 364
Additional query words: 4.00a 5.00a 5.00c 7.00a 97 98 XL98 XL97 XL7 XL5 XL4 count XL