Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




OpenOffice Calc 3.x Guide
Previous Page Home Next Page

Count and sum cells that match conditions: COUNTIF and SUMIF

The COUNTIF and SUMIF functions calculate their values based on search criteria. The search criteria can be a number, expression, text string, or even a regular expression. The search criteria can be contained in a referenced cell or it can be included directly in the function call.

The COUNTIF function counts the number of cells in a range that match specified criteria. The first argument to COUNTIF specifies the range to search and second argument is the search criteria. Table 7 illustrates different search criteria using the COUNTIF function referencing the data shown in Table 1.

The first two arguments for SUMIF serve the same purpose as the arguments for COUNTIF; the range that contains the cells to search and the search criteria. The third and final argument for SUMIF specifies the range to sum. For each cell in the search range that matches the search criteria, the corresponding cell in the sum range is added into the sum.

Table 7. Examples of search criteria for the COUNTIF and SUMIF functions.

Criteria Type Function Result Description
Number =COUNTIF(B1:C16; 95) 3 Find numeric values of 95.
Text =COUNTIF(B1:C16; "95") 3 Find numeric or text values of 95.
Expression =COUNTIF(B1:C16; ">95") 6 Find numeric values greater than 95.
Expression =COUNTIF(B1:C16; 2*45+5) 3 Find only numeric values of 95.
Regular expression =COUNTIF(B1:C16; "9.*") 12 Find numbers or text that start with 9.
Reference a cell =COUNTIF(B1:C16; B3) 3 Find 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.

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

  Published under the terms of the Creative Commons License Design by Interspire