Calc Guide

Table 18. Examples of search criteria for the COUNTIF and SUMIF
functions.
Criteria
Type
Function Result Description
Number =COUNTIF(B1:C16; 95) 3 Finds numeric values
of 95.
Text =COUNTIF(B1:C16; "95") 3 Finds numeric or text
values of 95.
Expression =COUNTIF(B1:C16;
">95")
6 Finds numeric values
greater than 95.
Expression =COUNTIF(B1:C16;
2*45+5)
3 Finds only numeric
values of 95.
Regular
expression
=COUNTIF(B1:C16; "9.*") 12 Finds numbers or
text that start with 9.
Reference
a cell
=COUNTIF(B1:C16; B3) 3 Finds a number or
number and text
depending on the
data type in cell B3.
Regular
expression
=SUMIF(A1:A16; "B.*";
B1:B16)
227 Sum Column B for
names in Col. A
starting with the
letter B.
Ignore filtered cells using SUBTOTAL
The SUBTOTAL function applies a function (see Table 19) to a range of
data, but it ignores cells hidden by a filter and cells that already
contain a SUBTOTAL. For example, =SUBTOTAL(2, "B2:B16") counts
the number of cells in B2:B16 that are not hidden by a filter.
Table 19. Function index for the SUBTOTAL function.
Function index Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
378 OpenOffice.org 3.x Calc Guide