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.
|
