What is the Microsoft Excel
COUNTIF Function?
The COUNTIF function is a computer
program that you run from a worksheet cell formula. It counts
how many cell values that meet a specified condition or criteria
within a given worksheet area (called a
range). You run the COUNTIF worksheet function by typing its
name in a formula then followed by the information it is suppose
to evaluate. The COUNTIF worksheet function is most commonly
used on rows or columns of information on the worksheet. The
function also has the capability to search for parts of text
within other text when evaluating its condition. The COUNTIF
worksheet function is good for determining how many of something
you have like how many of a certain part type were returned or
how many of a certain product were sold. It is also great logic
tester to see if data that is in one table exists in another.
How Do You Type the COUNTIF
Worksheet Function in a Formula?
Whenever you type a formula in a
worksheet cell, this is called syntax or grammar. The general
COUNTIF function syntax has a format like this when you type it
in a worksheet cell:
= COUNTIF( range, criteria )
Where range, criteria are called the
function argument list. Remember, you are running a computer
program at this point so the program needs information to
operate and that is why there is an argument list. When you see
an argument list and you see square brackets [ ] around the
argument name, this means the argument is optional and you do
not have to include it when typing unless you need it. So for
the syntax above, you need to include 2 arguments for the
COUNTIF function when typing it in a worksheet cell formula in
order for it to calculate correctly. What argument values can be
used are discussed below. Remember functions expect certain
things in their argument lists, if you do not put the correct
information in the list they will generate an error when run.
COUNTIF Worksheet Function
Argument Definitions
 range: This is the row or
column that you want evaluated by your criteria.
 criteria: This is the
condition that will be used to scan the designated row or
column. It can be in the form of a number, formula, function
or piece of text that defines which cells will be added. The
following relational operators and symbols can be used to
build the condition:
 Less than: <
 Greater than: >
 Less than or equal to:
<=
 Greater than or equal
to: >=
 Not equal to: <>
 Equal to =
 Concatenation Operator:
&
 Wild Card Characters: ?
and *
How Do You Run the COUNTIF
Function?
Since the COUNTIF function is a computer
program, it runs when you press Enter to enter the formula that
contains it. If any of the arguments are wrong, the function
will return an error.
What Do I Type for a COUNTIF
Function Argument?
When typing the COUNTIF function in a
worksheet cell formula, you need to replace the argument list
with arguments separating each one with a comma (,). Some
typical arguments you can use are:
Argument Type

Cell Formula

Example Explanation

Range References, = 
= COUNTIF( A1:A10, "=Widget1" ) 
Count all the values that match what is equal to
Widget1 in A1:A10 
Range References, > 
= COUNTIF( A1:A10, ">16" ) 
Count all the values that match what is greater than
16 in A1:A1 
Range References, < 
= COUNTIF( A1:A10, "<16" ) 
Count all the values that match what is less than 16
in A1:A1 
Range References, >= 
= COUNTIF( A1:A10, ">=16" ) 
Count all the values that match what is greater than
or equal to 16 in A1:A1 
Range References, <= 
= COUNTIF( A1:A10, "<=16" ) 
Count all the values that match what is less than or
equal to 16 in A1:A1 
Range References, <> 
= COUNTIF( A1:A10, "<>16" ) 
Count all the values that match what is not equal to
16 in A1:A10 
Range References,*txt 
= COUNTIF( A1:A10, "=*xsx" ) 
Count all the values that match what ends with xsx
in A1:A10 
Range References, txt* 
= COUNTIF( A1:A10, "=ca*" ) 
Count all the values that match what begins with ca
in A1:A10 
Range References, *txt* 
= COUNTIF( A1:A10, "=*91362*" ) 
Count all the values that match what contains 91362
in A1:A10 
Range References, &, Formula 
= COUNTIF( A1:A10, ">" & D1+E1 ) 
Count all the values that match what is greater than
D1+E1 in A1:A10 
Range References, &, Function 
= COUNTIF( A1:A10, ">" & SUM( G1:G4 ) ) 
Count all the values that match what is greater than
the sum of G1:G4 in A1:A10 
Column References, = 
= COUNTIF( A:A, "=Widget1" ) 
Count all the values that match what is equal to
Widget1 in column A 
Row References, = 
= COUNTIF( 1:1, "=Widget1" ) 
Count all the values that match what is equal to
Widget1 in row 1 
Range Names 
= COUNTIF( Products, "=Widget1" ) 
Count all the values where they equal Widget1 in the
range named Products* 
* It is possible to name a cell or
group of cells on a worksheet and use that name in place of
a range reference or cell reference. Consult Excel help on
how to name a cell.
Additional COUNTIF Function
Examples
The worksheet seen below contains some
typical worksheet formulas that run the COUNTIF worksheet
function. Pay close attention to the argument list and the
syntax used to write the formula.

A 
B 
C 
1 
Product 
Quantity 

2 
A 
100 

3 
B 
200 

4 
A 
300 

5 
B 
400 

6 
A 
500 

7 



8 

3 
=COUNTIF( A1:A6 , "=A" ) 
9 



10 
Region 
Quantity 

11 
1 
100 

12 
2 
200 

13 
3 
300 

14 
4 
400 

15 
5 
500 

16 
6 
600 

17 
7 
700 

18 
8 
800 

19 
9 
900 

20 
10 
100 

21 



22 

1 
= COUNTIF( A10:A20, "=7" ) 
23 

3 
= COUNTIF( A10:A20, ">7" ) 
24 

6 
= COUNTIF( A10:A20, "<7" ) 
25 

4 
= COUNTIF( A10:A20, ">=7" ) 
26 

7 
= COUNTIF( A10:A20, "<=7" ) 
27 

9 
= COUNTIF( A10:A20, "<>7" ) 
28 



29 


The COUNTIF function is used
as a logic tester to see if the Item X is contained in
the B30 to B35 range. Just like in a larger table
comparison. If COUNTIF returns an answer greater than 0,
then the IF function will
return Exists if not then it will return N/A. Very
simple but effective table comparison formula. 
30 
Master Item 
New Item 
= IF( COUNTIF( $B$30:$B$35,
"=" & A31) > 0 , "Exists", "N/A" ) 
31 
X 
Y 

32 
Y 
Z 

33 
Z 
A 

34 
A 
B 

35 
B 
C 

Things to Know About the
COUNTIF Function
 Counting things for
existence is an excellent logic test both in programming
and in Excel formulas. Be sure you also check out
COUNTA,
COUNT and
COUNTBLANK.
 You can use the wildcard
characters, question mark (?) and asterisk (*) in
criteria. A question mark matches any single character;
an asterisk matches any sequence of characters. If you
want to find an actual question mark or asterisk, type a
tilde (~) preceding the character.
 As a general rule, when
typing a condition, enclose the condition in quotes. For
example the condition count everything equal to widget1
would be constructed as "=Widget1". If you were looking
for everything = to 16 or greater than 16 they would be
written as "=16" or ">16". There are variations on these
rules, slang you can get away with, but quotes " " will
always work.
 You can use the & to
help construct the condition "on the fly" which means
the condition can be determined by formula. For example,
">" & A1+B1 would calculate to be ">3" if A1+B1 added up
to 3. This allows the condition to be flexible and not
"hard coded" which means static. It is called string
concatenation.
 If the condition is a
text evaluation, "=Widget1", it is not case sensitive
but is space sensitive so "=Widget1" is not the same as
"=Widget 1".
 Be sure to clean you
data before doing any type of text comparison. Spaces,
nonprintable characters, extra words should all be
removed before using the COUNTIF worksheet function. You
can use the CLEAN,
SUBSTITUTE and the
TRIM worksheet functions
for this task
 Use the COUNTIF function
to detect the presence of a value in a column before
using VLOOKUP or
MATCH. You can use it
as part of the logic argument for the
IF
Function also.
