Fabulous Examples of Excel Functions:
COUNTIF Basics
Microsoft Excel provides many built-in functions. In this tutorial, we discuss the COUNTIF function. For a discussion of advanced COUNTIF topics, please see Microsoft Excel Functions: COUNTIF Advanced.
Author: Patricia Lynn
An Excel function always begins with an equal = sign unless the function is imbedded inside of another function. For an introduction to Excel math, see our popular tutorial Excel Math Basics: A Beginner's Guide.
COUNTIF
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:
COUNTIF(range,criteria)
… 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 (~).
COUNTIF Examples
In the spreadsheet below, Column A and Column C contain textual data, while column B contains numeric data. Column E contains the functions - of which we see the values Excel has returned. We show what the functions look like in Column F.
Below the image is a detailed explanation of each example.
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.
Final Thoughts
It is advisable to lock any cell containing a function or formula, and then protect the worksheet, to prevent accidental erasure. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide.
When creating complicated functions and formulas, it is often helpful to type them into a Notepad file, and then copy and paste them into the cell. Notepad is a simple editor that comes free with Windows. Do NOT copy functions and formulas from Microsoft Word or other word processing programs.
Don't forget that every Excel function must begin with an equal = sign unless the function is imbedded inside of another function.We hope this article has been helpful. Cheers!
Back to Top
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-functions-countif.shtml">COUNTIF Basics</a>
