The COUNTIF Formula

The COUNTIF formula counts the number of cells that meets a condition or criteria.  The COUNTIF function will work nicely with logical operators such as greater than > less than < does not equal <> is equal, wildcards * ? for partial cell matching.  This article explains the logic of the COUNTIF formula with some practical examples.

COUNTIF with Text Criteria

The COUNTIF formula can be used to check how many cells in a range of cells meet a given condition.  Let’s say we have a list of countries in Column A and we want to check how many countries are USA.  Firstly the range that contains the countries and the criteria in the above example would be “USA” or a direct cell reference to USA.  The following will show both methods.  Firstly the direct cell reference as this is better practice. 

The above is a sample of countries that has the USA several times in the range.  Our task is to count the amount of times that USA appears in the range specified.

The Syntax for the COUNTIF formula is as follows:

=COUNTIF ( Criteria Range, Criteria)

Firstly the range that contains the countries and the criteria in the above example would be “USA” or a direct cell reference to USA.  The following will show both methods.  Firstly the direct cell reference as this is better practice. 

The COUNTIF formula becomes:

=COUNTIF(A2:A18, B2)

Next let’s look at what to do if we need to add a direct reference inside a formula.

The COUNTIF formula becomes:

=COUNTIF(A2:A18,"USA")

Extending the COUNTIF Formula

If we want to check the dataset for values that are greater than a given number we have two options.  We can place the operator in the cell or we can hard code the operator in the formula and refer to the cell with the value.  Let’s say we want to assess if the cells are above 200.

The COUNTIF formula becomes:

=COUNTIF(B2:B18, C2)

The above formula will assess the cells in Column B and return a count of the cells which are greater than  200 where cell C2 assessing the criteria has >200 in it.  Alternatively 200 could be placed in cell C2 and the formula becomes:

=COUNTIF(B2:B18,">"&C2)

The COUNTIF formula can do a range of other tasks such checking the exact match with a number or if data contains text or starts with certain text.  The following are a few examples. 

COUNTIF with Question Mark

Using the USA example the following will test for any characters after the first character.  Note – the question mark works one character at a time so it will check for S and A but needs 2 question marks.

The COUNTIF formula becomes:

=COUNTIF(A2:A18,"U??")

COUNTIF with Wildcard

Let’s use the wildcard character * to count all of the countries starting with A in the list.  The formula works by including the letter A followed by the wildcard character * in the formula.

The COUNTIF formula becomes:

=COUNTIF(A2:A18,"A*")

Note – If you are actually looking for a question mark or an multiplication symbol, use a tilde ~ in front question mark or multiplication sign for example. ~?, ~*.