Microsoft KB Archive/100790: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 54: | Line 54: | ||
</div> | </div> | ||
where | 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 | 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= | =SUM(IF(FREQUENCY(IF(A1:C4="XXX",ROW(A1:C4)), IF(A1:C4="XXX",ROW(A1:C4)))>0,1,0)) | ||
</div> | </div> | ||
This formula returns the value 3, indicating that | 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 == | ||
"Function Reference," version 4.0, pages 178-179, 364 | |||
</div> | </div> |
Revision as of 13:54, 19 July 2020
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