Documentation/SL/How Tos/Calc: funkcija COUNTIF
From Apache OpenOffice Wiki
< Documentation | SL/How Tos
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
COUNTIF
Prešteje celice v danem obsegu, ki ustrezajo navedenemu pogoju.
Skladnja:
COUNTIF(test_range; pogoj)
- test_range je obseg, to be tested.
- pogoj je lahko:
- število, kot je 34,5
- izraz, kot je 2/3 ali SQRT(B5)
- besedilni niz
- 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.
Primeri:
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.
Napredna tema:
SUMPRODUCT(B2:B8="Red").
- returns the number of cells in B2:B8 matching Red, with case sensitivity. See Conditional Counting and Summation for details.
