Microsoft KB Archive/100790

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


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

Article ID: 100790

Article Last Modified on 8/15/2003



APPLIES TO

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 98 for Macintosh



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: 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.

REFERENCES

"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

Keywords: KB100790