COUNTIF counts the values in a range of cells that match a given criteria. In this guide, we’re going to show you how to use the COUNTIF function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions


COUNTIF(range, criteria)



The range of cells that you want to apply the criteria against


The criteria to be applied to the range values, and determine which cells are to be counted.


We’re using named ranges in our examples to make the formulas easier to read. You don’t need to do this to run your formulas.

Example 1

 formula counts Sp.Atk values, where Sp.Atk is less than 100.


Example 2

formula sums the HP values, where Type starts with the string “FIRE“. If you use “FIRE” without asterisks, the COUNTIF function will skip the HP values for “FIRE, GROUND” and “FIRE, FLYING“.

Note that the COUNTIF function is not case-sensitive – “FIRE” and “fire” criteria will give the same results.


Download Workbook


Comparison operators:



Criteria Sample

Criteria Meaning


Equal to


Equal to 10000


Not equal to


Not equal to 10000

Greater than


Greater than 10000

Less than


Less than 10000


Greater than or equal to


Greater than or equal to 10000


Less than or equal to


Less than or equal to 10000


Takes the place of a single character


6-character word starts by “Admin”


Can take the place of any number of characters.


Any number of character word starts with “Admin”


Use tilde in front of a  question mark or an asterisk to actually find them


Equal to “Admin*”

Note: Wildcards cannot be used for numeric values. Searching for a wild card character in a range of numeric values will return no matches.



The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters, or the string #VALUE!.

Empty Cell Reference

If criteria argument refers to an empty cell, the COUNTIF function evaluates the cell value as 0.