Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL2000: Formula to Count the Number of Rows in Which an Item Appears

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

View products that this article applies to.

This article was previously published under Q214079

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Keywords: KB214079, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 214079
Revision : 4
Created on : 9/27/2003
Published on : 9/27/2003
Exists online : False
Views : 269