Fabulous Examples of Excel Functions:
The COUNTIF Function - Basics
The COUNTIF function is a powerful Excel function that tallys a cell count based on specified criteria. This tutorial is an introduction to COUNTIF.
For an advanced discussion of the COUNTIF function using operators other than equals (=), more examples of wildcards, and the use of concatenation, please see our COUNTIF Function: Advanced tutorial.
For our students that learn best via video, we recommend Excel 2010 Tutorial for Beginners. We have this course and thnk it's a super value. And you can watch some chapters online for free.
The COUNTIF Function
This Excel function allows you to display the number of cells in a range whose values meet specific criteria. The syntax of the COUNTIF function is:
where range is a group of cells, and criteria is the value a cell must have to be counted. The default action for criteria is "equals" and should not be specified. When operators are used, special rules must be followed, which are covered in our Advanced tutorial.
- =COUNTIF(A1:A9,5) This Excel function counts the number of cells in A1 through A9 whose value equals 5. Remember that the default action is equals and the equal sign should not be included in the criteria.
- =COUNTIF(D2:D8,"Smith") This Excel function counts the number of cells in D2 through D8 whose value equals Smith. Text values must be wrapped in quotation marks.
Wildcards Used in Criteria
The wildcard characters asterisk (*) and question mark (?) may be used for text values. An asterisk matches any sequence of characters (e.g. "S*" matches any value beginning with the letter S). The question mark represents one character (e.g. "S????" matches 5-digit values beginning with the letter S).
If you want to use * or ? as text in criteria, it must be preceded by a tilde (~).
In the spreadsheet below, Columns A, B, and C contain either numeric or text data. Column F shows six different functions. Each function has been typed into the corresponding cell in Column E. In other words, the function displayed in cell F1 was typed into cell E1 and the value of that function is shown in cell E1, and so on.
Study each function carefully and then read the explanation for each of the six functions below in the Examples.
Example 1: Count the cells in a range whose values equal a specific string of text
=COUNTIF(A1:A7,"MS Excel"): This function asks Excel to count the number of cells in A1:A7 whose value equals "MS Excel." Since the action defaults to "equals," no sign is required. Excel returns a value of 3.
Example 2: Count the cells in a range that are equal to the contents of a particular cell
Example 2 =(COUNTIF(A1:A7,A2)): When asking Excel to count cells whose value equals the content of a cell, we can enter the cell name as the criteria. Here we ask Excel to count the number of cells in A1:A7 that contain the value in A2, which is OpenOffice. Excel returns a value of 2.
Example 3: Count the cells in a range whose values equal a specific string of text with a wildcard
=COUNTIF(A1:A7,"MS*"): Wildcard characters are explained above. Here we ask Excel to count how many cells in A1:A7 whose value begins with MS. Excel returns a value of 5.
Example 4: Count the cells in a range whose values equal either of two strings of text
=COUNTIF(A1:A7,"MS Word")+COUNTIF(A1:A7,"MS Excel"): This function asks Excel to add the number of cells in A1:A7 whose value equals MS Word, to the number of cells in the same range whose value equals MS Excel - a total of 4.
This is not the same as asking Excel to count the number of records meeting two criteria. The COUNTIF function cannot filter for multiple criteria.
Example 5: Calculate the percentage of cells in a range that meet certain criteria
=COUNTIF(C1:C7,"Y")/ROWS(C1:C7): In this example, we ask Excel determine the percentage of cells in C1:C7 have a value of "Y." Excel counts the cells whose values equal "Y" and then divides that number by the total number of cells in the range. The simple ROWS function is used to count the number of rows in the range.
The real value of 4 ÷ 7 equals .571428....etc. Since we formatted our cell as a percentage with no decimal places, Excel displays 57%.
Example 6: Count the cells in a range whose values fall within a specific number range
=COUNTIF(B1:B7,">=2")-COUNTIF(B1:B7,">7"): In this useful application of the COUNTIF function, we ask Excel to count the number of cells in B1:B7 whose values are 2 through 7. Since we are using operators, both criteria must be enclosed in double quotation marks.
The first COUNTIF function counts the cells with a value ≥ 2, (5). The second COUNTIF function counts the cells whose values are > 7 (1). Excel subtracts 1 from 5 and returns a value of 4.
We hope this article has been helpful. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-functions/countif-function.shtml">The COUNTIF Function - Basics</a>
- » AND
- » AND with IF
- » AVERAGE
- » COUNT
- » COUNTA
- » COUNTBLANK
- » COUNTIF - Basics
- » COUNTIF - Advanced
- » Date and Time: How Excel Handles
- » DATEVALUE
- » EVEN and ODD
- » IF
- » IF (Nested)
- » MAX
- » MEDIAN
- » MIN
- » MODE
- » MROUND
- » Nested IF Functions
- » NETWORKDAYS
- » NOW
- » ODD and EVEN
- » OR
- » OR with IF
- » POWER
- » PRODUCT (Multiplying)
- » ROUND
- » ROUNDDOWN and ROUNDUP
- » SQRT
- » SUM
- » SUMPRODUCT
- » SUMSQ
- » TODAY
- » TRUNC
Search Keynote Support