XL2000: Formula to Count the Number of Rows in Which an Item Appears (214079)
The information in this article applies to:
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(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 that 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.
Modification Type: | Major | Last Reviewed: | 9/27/2003 |
---|
Keywords: | kbhowto kbinfo KB214079 |
---|
|