Fabulous Examples of Excel Functions:
COUNTIF Advanced
This tutorial discusses advanced features of the COUNTIF function such as operators, wildcards, and concatenation. Included are a variety of examples to help explain this function's many uses.
Author: Patricia Lynn
For an excellent video-based learning course on Excel, check out Excel 2010 Tutorial for Beginners. We've reviewed this course (over 9 hours of hands-on lessons) and found it very well done.
If you are unfamiliar with the Microsoft Excel function of COUNTIF, you may want to begin by reading our tutorial Microsoft Excel Functions: COUNTIF Basics. Also, please note that additional counting functions of COUNT, COUNTA, and COUNTBLANK are explained in our tutorial Microsoft Excel: The COUNTing Functions .
Let's begin!
COUNTIF
This Excel function allows you to display the number of cells in a range whose values meets 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. Remember that the default operator for criteria is "equals" and should not be specified.
Review of Operators
Operators ( >, <, >=, <=, <> and =) must be enclosed in quotation marks. The default operand of equals (=) does not need to be specified, and <> means "not equal.
As discussed below, the ampersand (&) is needed in some criteria to indicate concatenation.
Review of Wildcards
For text values, the wildcard character asterisk (*) can be used to match any sequence of characters (e.g. "S*" matches any value beginning with the letter S). The ? wildcard character (question mark) can be used to represent one character only (e.g. "S????" matches 5-digit values beginning with the letter S).
The * or ? used in criteria as text must be preceded by a tilde (~).
Advanced COUNTIF Examples: Operators and Concatenation
Example 1: Count the cells in a range whose values do not equal a string of text
=COUNTIF(A1:A7,"<>Smith") When using an operator, wrap the entire criterion with quotes as shown. This Excel function counts the cells in A1:A7 whose values do not equal Smith, and returns a value of 5. Excel will include in the count cells that are empty, contain numbers, or contain error messages.
Many tutorials instruct users to concatenate every value that follows an operator. For instance, it is perfectly OK to write the function as =COUNTIF(A1:A7,"<>"&"Smith"). It just isn't necessary.
Example 2: Count the cells in a range that are < the contents of a particular cell
=COUNTIF(B1:B7,"<"&B1)When using an operator and referencing a cell name in the criteria, two rules must be followed. First, the operator must be enclosed in quotation marks. Second, an & - the concatenation operator - must precede the cell name so that Excel knows you are referencing a cell name. Without the & Excel will look for text in the cells that equals B1.
Referencing a cell name with an operator is one of the few instances that concatenation must be specified.
Example 3: Count the number of cells in a range that do not contain text
=COUNTIF(A1:A7,"<>*") In this example, the criterion must be wrapped in quotation marks since we are using an operator. We use the wildcard * to refer to any text. Excel counts the number of cells that do not contain text and returns a value of 1 (cell A4).
Excel will count all cells other than cells containing text: empty cells, cells with numbers, and cells with error messages. Excel will not count, however, cells that have been cleared of data by pressing the space bar. Clearing cells this way places a blank space character in the cell. Use the delete key to clear the contents of a cell instead.
Example 4: Count the number of cells in a range that are not empty
=COUNTIF(A1:A7,"<>"&"")In Excel, a blank or empty cell (one that does not contain a blank space character) is referenced by a pair of double quotation marks, "". This example counts the number of cells in A1:A7 that are not empty. We wrap the operator in quotation marks "<>" - and then place the concatenate operator & before the pair of double quotation marks symbolizing 'empty.' Excel returns a value of 6.
Example 5: Count the number of cells in a range containing text, and whose first initial is greater than or less than a specific letter.
=COUNTIF(A1:A7,"<N") This application of the COUNTIF function is useful for counting the number of cells whose values are in the first half of the alphabet (A-M) or the last half of the alphabet (N-Z). However, when working with criteria of this nature, strict rules must be followed or Excel will not return the value you expect.
When counting the number of cells in the lower half of the alphabet, the criterion should read "<N" (and not "<=M"). Always use < on the first letter after the range's upper limit.
And when counting the number of cells in the upper half of the alphabet, code ">=N" (not ">M"). Always use >= on the lowest letter of the range.
Example 6: Count the cells in a range that are greater than the average of the cells in that range
=COUNTIF(B1:B7,">"&AVERAGE(B1:B7)) This useful application of the COUNTIF function counts the number of cells whose values are above average, but can be modified, or course, to count cells that are less than average or equal to the average. The operator must be wrapped in quotation marks and an & placed before the AVERAGE function. The average of the 7 numbers in this example is 50, so Excel returns a value of 3. For more information on the AVERAGE function, see Microsoft Excel Functions: AVERAGE, MEDIAN, MODE, MAX, and MIN.
Example 7: Calculate the percentage of cells in a range that meet certain criteria - excluding cells without text
=COUNTIF(A1:A7,"Smith")/(ROWS(A1:A7)-COUNTIF(A1:A7"<>*")) This example illustrates how to ask Excel to determine what percent as particular text value is compared to all of the cells in the range with text values. It can be modified with wildcards; or empty cells could also be eliminated from the dividend before calculating the percentage. Do remember that Excel does not consider a cell empty if the space is pressed when the cell is active. Use the delete key to clear a cell of all data.
We begin with a COUNTIF function that counts the cells containing Smith (2). We then divide that number by the number of rows, as determined by the ROWS function (7), less the number of cells without text values (1).
Notice that the last two functions must be enclosed in parentheses so Excel will subtract the cells without text values before dividing. Without these parentheses, Excel would perform the division first. (Mathematical Order of Operations is important to understand and is discussed in our tutorial Microsoft Excel: Math Basics). Our equation reads: 2 ÷ (7 - 1). Format the cell as a percentage.
In Conclusion
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.
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-advanced.shtml">COUNTIF Advanced</a>
