Difference between revisions of "Documentation/How Tos/Calc: COUNTIF function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m (→See also:) |
(Revision of Syntax section) |
||
| Line 7: | Line 7: | ||
<tt>'''COUNTIF(test_range; condition)'''</tt> | <tt>'''COUNTIF(test_range; condition)'''</tt> | ||
| − | + | : <tt>'''test_range'''</tt> is the range to be tested. | |
| − | <tt>'''condition'''</tt> | + | : <tt>'''condition'''</tt> may be: |
| − | :<tt>''' | + | :: a number, such as <tt>'''34.5'''</tt> |
| + | :: an expression, such as <tt>'''2/3'''</tt> or <tt>'''SQRT(B5)'''</tt> | ||
| + | :: a text string | ||
| − | + | : <tt>'''COUNTIF'''</tt> counts those cells in <tt>'''test_range'''</tt> that are equal to <tt>'''condition'''</tt>, unless <tt>'''condition'''</tt> is a text string that starts with a comparator: | |
| − | ::<tt>''' | + | :: <tt>'''>'''</tt>, <tt>'''<'''</tt>, <tt>'''>='''</tt>, <tt>'''<='''</tt>, <tt>'''='''</tt>, <tt>'''<>'''</tt> |
| − | + | : In this case <tt>'''COUNTIF'''</tt> compares those cells in <tt>'''test_range'''</tt> with the remainder of the text string (interpreted as a number if possible or text otherwise). | |
| + | : For example the condition “<tt>'''>4.5'''</tt>” tests if the content of each cell is greater than the number 4.5, and the condition “<tt>'''<dog'''</tt>” tests if the content of each cell would come alphabetically before the text <tt>'''dog'''</tt>. | ||
| − | |||
| + | : It can be very important to check the settings on the '''Tools menu – Options - OpenOffice.org Calc - Calculate''' dialog: | ||
| − | If the checkbox is ticked for ''<nowiki>search criteria = and <> must apply to whole cells</nowiki>'', then the condition “<tt>'''red'''</tt>” will match only <tt>'''red'''</tt><nowiki>; if unticked it will match </nowiki><tt>'''red'''</tt>, <tt>'''Fred'''</tt>, <tt>'''red herring'''</tt>. | + | :: If the checkbox is ticked for ''<nowiki>search criteria = and <> must apply to whole cells</nowiki>'', then the condition “<tt>'''red'''</tt>” will match only <tt>'''red'''</tt><nowiki>; if unticked it will match </nowiki><tt>'''red'''</tt>, <tt>'''Fred'''</tt>, <tt>'''red herring'''</tt>. |
| + | :: If the checkbox is ticked for ''Enable regular expressions in formulas'', the condition will match using [[Documentation/How_Tos/Regular Expressions in Calc|regular expressions]] - so for example "<tt>'''r.d'''</tt>" will match <tt>'''red'''</tt>, <tt>'''rod'''</tt>, <tt>'''rid'''</tt>, and "<tt>'''red.*'''</tt>" will match <tt>'''red'''</tt>, <tt>'''redraw'''</tt>, <tt>'''redden'''</tt>. | ||
| − | + | :: The checkbox for ''Case sensitive'' has no effect (no attention is paid to case). See the examples for how to achieve a case sensitive match. | |
| − | + | : Blank (empty) cells in <tt>'''test_range'''</tt> are ignored (they never satisfy the condition). | |
| − | + | : <tt>'''condition'''</tt> can only specify one single condition. See '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' for ways to specify multiple conditions. | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | <tt>'''condition'''</tt> can only specify one single condition. See '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' for | + | |
=== Example: === | === Example: === | ||
| Line 43: | Line 43: | ||
<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. |
| − | <tt>''' | + | <tt>'''COUNTIF(C2:C8; "<"&F2)'''</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>. (<i>Advanced topic:</i> this works because the & operator converts the content of <tt>'''F2'''</tt> to text, and concatenates it with "<"; <tt>'''COUNTIF'''</tt> then converts it back to a number). |
<tt>'''COUNTIF(A2:A8; ">=P")'''</tt> | <tt>'''COUNTIF(A2:A8; ">=P")'''</tt> | ||
| Line 53: | Line 53: | ||
<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. | ||
| + | |||
| + | <i>Advanced topic:</i> | ||
| + | |||
| + | <tt>'''SUMPRODUCT(B2:B8="Red")'''</tt>. | ||
| + | :returns the number of cells in <tt>'''B2:B8'''</tt> matching <tt>'''Red'''</tt>, with case sensitivity. See [[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]] for details. | ||
=== See also: === | === See also: === | ||
| Line 59: | Line 64: | ||
'''[[Documentation/How_Tos/Calc: COUNTA function|COUNTA]]''', | '''[[Documentation/How_Tos/Calc: COUNTA function|COUNTA]]''', | ||
'''[[Documentation/How_Tos/Calc: COUNTBLANK function|COUNTBLANK]]''' | '''[[Documentation/How_Tos/Calc: COUNTBLANK function|COUNTBLANK]]''' | ||
| + | |||
| + | '''[[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]]''' | ||
'''[[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 05:31, 7 May 2008
COUNTIF
Counts the number of cells in a range that meet a specified condition.
Syntax:
COUNTIF(test_range; condition)
- test_range is the range to be tested.
- condition may be:
- a number, such as 34.5
- an expression, such as 2/3 or SQRT(B5)
- a text string
- COUNTIF counts those cells in test_range that are equal to condition, unless condition is a text string that starts with a comparator:
- >, <, >=, <=, =, <>
- In this case COUNTIF compares those cells in test_range with the remainder of the text string (interpreted as a number if possible or text otherwise).
- For example the condition “>4.5” tests if the content of each cell is greater than the number 4.5, and the condition “<dog” tests if the content of each cell would come alphabetically before the text dog.
- It can be very important to check the settings on the Tools menu – Options - OpenOffice.org Calc - Calculate dialog:
- 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.
- The checkbox for Case sensitive has no effect (no attention is paid to case). See the examples for how to achieve a case sensitive match.
- 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 ways to specify multiple conditions.
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. (Advanced topic: this works because the & operator converts the content of F2 to text, and concatenates it with "<"; COUNTIF then converts it back to a number).
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.
Advanced topic:
SUMPRODUCT(B2:B8="Red").
- returns the number of cells in B2:B8 matching Red, with case sensitivity. See Conditional Counting and Summation for details.
See also:
SUMIF, COUNT, COUNTA, COUNTBLANK
