Difference between revisions of "Documentation/How Tos/Calc: COUNTIF function"
m |
m |
||
| Line 40: | Line 40: | ||
{| border="0" | {| border="0" | ||
| − | |<tt>'''COUNTIF(C2:C8; ">=20")'''</tt> | + | |-valign="top" |
| + | |width="180pt"|<tt>'''COUNTIF(C2:C8; ">=20")'''</tt> | ||
|returns the number of cells in <tt>'''C2:C8'''</tt> whose contents are numerically greater than or equal to <tt>'''20'''</tt>. | |returns the number of cells in <tt>'''C2:C8'''</tt> whose contents are numerically greater than or equal to <tt>'''20'''</tt>. | ||
| − | |- | + | |-valign="top" |
|<tt>'''COUNTIF(C2:C8; F1)'''</tt> | |<tt>'''COUNTIF(C2:C8; F1)'''</tt> | ||
|where <tt>'''F1'''</tt> contains the text <tt>'''>=20'''</tt> returns the same number. | |where <tt>'''F1'''</tt> contains the text <tt>'''>=20'''</tt> returns the same number. | ||
| − | |- | + | |-valign="top" |
|<tt>'''<nowiki>COUNTIF(C2:C8; "<"&F2)</nowiki>'''</tt> | |<tt>'''<nowiki>COUNTIF(C2:C8; "<"&F2)</nowiki>'''</tt> | ||
|where <tt>'''F2'''</tt> contains <tt>'''20'''</tt> returns the number of cells in <tt>'''C2:C8'''</tt> whose contents are numerically less than <tt>'''20'''</tt>. | |where <tt>'''F2'''</tt> contains <tt>'''20'''</tt> returns the number of cells in <tt>'''C2:C8'''</tt> whose contents are numerically less than <tt>'''20'''</tt>. | ||
| − | |- | + | |-valign="top" |
|<tt>'''COUNTIF(A2:A8; ">=P")'''</tt> | |<tt>'''COUNTIF(A2:A8; ">=P")'''</tt> | ||
|returns the number of cells in <tt>'''A2:A8'''</tt> whose contents begin with the letter <tt>'''P'''</tt> or later in the alphabet. | |returns the number of cells in <tt>'''A2:A8'''</tt> whose contents begin with the letter <tt>'''P'''</tt> or later in the alphabet. | ||
| − | |- | + | |-valign="top" |
|<tt>'''COUNTIF(B2:B8;"red")'''</tt> | |<tt>'''COUNTIF(B2:B8;"red")'''</tt> | ||
|returns the number of cells in <tt>'''B2:B8'''</tt> containing <tt>'''red'''</tt>, but this number may depend on the option settings discussed above. | |returns the number of cells in <tt>'''B2:B8'''</tt> containing <tt>'''red'''</tt>, but this number may depend on the option settings discussed above. | ||
| Line 62: | Line 63: | ||
=== See also: === | === See also: === | ||
| − | '''[[Documentation/How_Tos/Calc: SUMIF function|SUMIF function]]''', '''[[Documentation/How_Tos/Calc: COUNTBLANK function|COUNTBLANK function]]''' | + | '''[[Documentation/How_Tos/Calc: SUMIF function|SUMIF function]]''', |
| + | '''[[Documentation/How_Tos/Calc: COUNT function|COUNT function]]''', | ||
| + | '''[[Documentation/How_Tos/Calc: COUNTA function|COUNTA function]]''', | ||
| + | '''[[Documentation/How_Tos/Calc: COUNTBLANK function|COUNTBLANK function]]''' | ||
'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | ||
'''[[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]]''' | '''[[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]]''' | ||
Revision as of 20:31, 30 November 2007
COUNTIF
Counts the number of cells in a range that meet a specified condition.
Syntax:
COUNTIF(test_range; condition)
This function counts those cells in the range test_range that meet the condition.
condition is or refers to a text string which has the following format:
- comparator value where
- comparator is one of >, <, >=, <=, =, <> (if comparator is omitted = is assumed ), and
- value is the value (number or text) to be compared.
For example the condition “>4” tests if the contents of the cell are greater than 4.
Facilities to compare text for equality or inequality (= or <>) are very powerful, but some care must be taken. You must choose the desired behaviour from the Tools menu – Options - OpenOffice.org Calc - Calculate:
If the checkbox is ticked for search criteria = and <> must apply to whole cells, then the condition “red” will match only red; if unticked it will match red, Fred, red herring.
If the checkbox is ticked for Enable regular expressions in formulas, the condition will match using regular expressions - so for example "r.d" will match red, rod, rid, and "red.*" will match red, redraw, redden.
At present the checkbox for Case sensitive has no effect (no attention is paid to case) - but this may be changed in future, so please do not rely on this behaviour.
Blank (empty) cells in test_range are ignored (they never satisfy the condition).
condition can only specify one single condition. See Conditional Counting and Summation for more information.
Example:
| COUNTIF(C2:C8; ">=20") | returns the number of cells in C2:C8 whose contents are numerically greater than or equal to 20. |
| COUNTIF(C2:C8; F1) | where F1 contains the text >=20 returns the same number. |
| COUNTIF(C2:C8; "<"&F2) | where F2 contains 20 returns the number of cells in C2:C8 whose contents are numerically less than 20. |
| COUNTIF(A2:A8; ">=P") | returns the number of cells in A2:A8 whose contents begin with the letter P or later in the alphabet. |
| COUNTIF(B2:B8;"red") | returns the number of cells in B2:B8 containing red, but this number may depend on the option settings discussed above. |
See also:
SUMIF function, COUNT function, COUNTA function, COUNTBLANK function
