Microsoft KB Archive/100790: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
Line 54: Line 54:


</div>
</div>
where &quot;range&quot; is the range that you want to search and &quot;item&quot; 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 &quot;item.&quot;<br />
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."<br />
<br />
<br />
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.
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.
Line 63: Line 63:
== MORE INFORMATION ==
== MORE INFORMATION ==


For example, to find out how many rows in which &quot;XXX&quot; appears within the following range
For example, to find out how many rows in which "XXX" appears within the following range
<pre class="fixed_text">  A1: XXX        B1: XXX        C1: XXX
<pre class="fixed_text">  A1: XXX        B1: XXX        C1: XXX
   A2: YYY        B2: XXX        C2: XXX
   A2: YYY        B2: XXX        C2: XXX
Line 72: Line 72:
<div class="indent">
<div class="indent">


=SUM(IF(FREQUENCY(IF(A1:C4=&quot;XXX&quot;,ROW(A1:C4)), IF(A1:C4=&quot;XXX&quot;,ROW(A1:C4)))&gt;0,1,0))
=SUM(IF(FREQUENCY(IF(A1:C4="XXX",ROW(A1:C4)), IF(A1:C4="XXX",ROW(A1:C4)))&gt;0,1,0))




</div>
</div>
This formula returns the value 3, indicating that &quot;XXX&quot; is contained in three rows of the range.
This formula returns the value 3, indicating that "XXX" is contained in three rows of the range.


</div>
</div>
Line 83: Line 83:
== REFERENCES ==
== REFERENCES ==


&quot;Function Reference,&quot; version 4.0, pages 178-179, 364
"Function Reference," version 4.0, pages 178-179, 364


</div>
</div>

Revision as of 12:54, 19 July 2020

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