COUNTIF function is one of the statistical functions in Excel that is a combination of COUNT and IF functions or the COUNTA function. When used in fomula, function counts the number of cells that match specific criteria or conditions in the same or multiple ranges. The COUNTIF function helps count cells containing text, numbers, or dates that meet specific criteria.

You can count cells using COUNTIF or COUNTIFS functions in Excel. The difference between COUNTIF and COUNTIFS functions is that COUNTIF is used for counting cells that meet one criterion in one range, while COUNTIFS counts cells that fulfill multiple conditions in the same or multiple ranges.

This article will demonstrate you how to use the two functions COUNTIF and COUNTIFS in Excel.

Excel COUNTIF Function

The COUNTIF function enables you to perform data counts based on a specific criterion or condition. The condition used in the function works with logical operators (<, >, <>, =, >=, <=) and wildcards characters (*, ?) for partial matching.

Syntax of COUNTIF Function

The structure of a COUNTIF function is:

=COUNTIF(range,criteria)

Parameters:

  • range – The range of cells to count.
  • criteria – The condition determines which cells should be included in count in the specified range. Criteria can be a numeric value, text, reference to a cell address or equation.

Using COUNTIF Function to Count Numeric Values

As we discussed above, the criteria (second argument) in the COUNTIF function defines the condition that tells the function which cells to count.

This function helps you count the number of cells with values that meet logical conditions such as equal to, greater than, less than, or not equal to a specified value, etc.

In the below example, the formula counts cells that contain a value equal to 5 (criteria). You can directly insert ‘5 in the formula or use reference to the cell address that has the value (cell D2 in the below example).

=COUNTIF(B2:B11,D2)

The above formula counts the number of cells in the cell range (B2:B11) that contain the value equal to the value in cell D2.

The following formula counts the cells that has value less than 5.

=COUNTIF(B2:B11,"<5")

The less than operator (<) tells the formula to count cells with a value less than ‘5’ in the range B2:B11. Whenever you use an operator in condition, make sure to enclose it double quotes (“”).

Sometimes when you want to count the cells by examining them against a criterion (value) in a cell. In such cases, make a criterion by joining an operator and a cell reference. When you do that, you need to enclose the comparison operator in double quotes (“”), and then place an ampersand (&) between the comparison operator and the cell reference.

=COUNTIF(B2:B11,">="&D2)

The picture below shows a few example formulas and their result.

Using COUNTIF Function to Count Text Values

To count cells that contain certain text strings, use that text string as the criteria argument or the cell that contains a text string. For example, in the below table, if we want to count all the cells in the range (B21:D27) with the text value in cell B21 (sam) in it, we can use the following formula:

=COUNTIF(B21:D27,B21)

As we discussed before, we could either use the text ‘sam’ directly in the formula or use a cell reference that has the criteria (B21). A text string should always be enclosed in double-quotes (“”) when it is used in a formula in excel.

=COUNTIF(B21:D27,"sam")

To count cells that do not contain a specified text, use the below formula:

=COUNTIF(B21:D27,"<>"&B21)

Make sure to enclose the ‘not equal to’ "<>" operator in double quotes.

If you are using the text ‘sam’ directly in the formula, you need to enclose the ‘<>’ operator and text string together ("<>sam") in double-quotes.

=COUNTIF(B21:D27,"<>sam")

Using Wildcards in Excel COUNTIF Function (Partial Matching)

You can use the COUNTIF formula with wildcard characters to count cells that contain a specific word, phrase, or letters. There are three wildcard characters you can use in the Excel COUNTIF function:

  • * (asterisk) – It is used to count cells with any number of starting and ending characters/letters. (e.g., St* could mean Stark, Stork, Stacks, etc.
  • ? (question mark) – It is used to find cells with any single character. (e.g., St?rk could mean Stark or Stork.
  • ~ (tilde) – It is used to find and count the number of cells containing a question mark or asterisk character (~, *, ?) in the text.

Counting Cells Starting or Ending with Certain Characters

To count the cells that begin or end with specific text with any number of other characters in a cell, use an asterisk (*) wildcard in the second argument of the COUNTIF function.

Use these sample formula:

=COUNTIF(A1:A10,"A*") – to count cells that starts with “A”.

=COUNTIF(A19:A28,"*er") – to count number of cells that end with the characters “er”.

=COUNTIF(A2:A12,"*QLD*") – for counting the cells that contain the text “QLD” anywhere in the text string.

A ? represents exactly one character, use this wildcard in COUNTIF function below to count the number of cells that contain exactly +1 character where ‘?’ is used.

=COUNTIF(A1:A10,"Par?s")

Counting Empty and Non-Empty Cells with COUNTIF Function

The COUNTIF formula is also helpful when it comes to counting the number of empty or non-empty cells in a given range.

Count Non-Blank Cells

If you want to count only cells that contain any ‘text’ values, use the below formula. This formula considers cells with dates and numbers as empty cells and won’t include them in in the count. 

=COUNTIF(A1:B12,"*")

The wildcard * matches with only the text values and returns the count of all text values in the given range.

If you want to count all non-empty cells in a given range, try this formula:

=COUNTIF(A1:B12,"<>")

Count Blank Cells

If you want to count blank cells in a certain range, use the COUNTIF function with the * wildcard character and <> operator in the criteria argument to count empty cells.

This formula counts cells that doesn’t contain any text values:

=COUNTIF(A1:B12,"<>"&"*")

Since * wildcard matches with any text value, the above formula will count all the cells not equal to *. It counts cells with dates and numbers as blanks as well.

To count all blanks (all value types):

=COUNTIF(A1:B12,"")

This function counts only empty cells in the range.

Using COUNTIF Function to Count Dates

You can count cells with dates (same as you did with number criteria) that meet a logical condition or the specified date or date in the reference cell.

To count the cells that contain the specified date (05-05-2020), we would use this formula:

=COUNTIF(B2:B10,"05-05-2020")

You can also specify a date in different formats as the criteria in the COUNTIF function like it’s shown below:

If you want to count cells that contain dates before or after a certain date, use the less than (before) or greater than (after) operators along with the specific date or cell reference.

=COUNTIF(B2:B10,">=05/05/2020")

You can also use a cell reference that contains a date by combining it with the operator (within double quotes).

To count the number of cells in the range A2:A14 with a date before the date in E3, use the below formula, where greater than (<) operator means before the date in E3.

=COUNTIF(A2:A14,"<"&E3)

A few example formulas and their result:

Count Date based on Current Date

You can combine the COUNTIF function with specific Excel’s Date functions i.e., TODAY() to count cells that have the current date. 

=COUNTIF(A2:A14,">"&TODAY())

This function count all the dates from today in the range (A2:A14).

Count Dates between a Specific Date Range

If you want to count all dates between two dates, you need to use two criteria in the formula.

We can do this by using two methods: COUNTIF and COUNTIFS functions.

Using Excel COUNTIF function

You need to use two COUNTIF functions to count all the dates between the two specified dates.

To count the dates between ’09-02-2020′ and ’20-08-2021′, use this formula:

=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")

This formula first finds the number of cells that have a date after February 2 and subtracts the count of cells with dates after August 20. Now we get the no. of cells that have dates that come after February 2 and on or before August 20 (count is 9).

If you don’t want the formula to count both February 2 and August 20, use this formula instead:

=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")

Just replace ‘>’ operator with ‘>=’ in the second criteria.

Using Excel COUNTIFS function

The COUNTIFS function supports multiple criteria too and unlike, COUNTIF function, it counts the cells only after all the conditions are met. If you want to count cells with all the dates between two specified dates, enter this formula:

=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)

If you wish to include the specified dates as well in the count, use ‘>=’ and ‘<=’ operators. Here, go with this formula:

=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")

We used date directly in the criteria instead of cell reference for this example.

How to Handle COUNTIF and COUNTIFS with Multiple Criteria in Excel

COUNTIF function is mostly used for counting cells with single criteria(condition) in one range. But you can still use COUNTIF to count cells that match multiple conditions in the same range. However, the COUNTIFS function can be used to count cells that meet multiple conditions in the same or different ranges.

How to Count Numbers Within a Range

You can count cells containing numbers between the two specified numbers using two functions: COUNTIF and COUNTIFS.

COUNTIF to Count Numbers Between Two Numbers

One of the common uses for the COUNTIF function with multiple criteria is counting the numbers between two specified numbers, e.g. to count numbers greater than 10 but less than 50. To count numbers within a range, conjoin two or more COUNTIF functions together in one formula. Let us show you how.

Let’s say you want to count cells in the range B2:B9 where a value is greater than 10 and less than 21 (not including 10 and 21), go with this formula:

=COUNTIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")

The difference between two numbers is found by subtracting one formula from another. The first formula counts the numbers greater than 10 (which is 7), the second formula returns the count of numbers greater than or equal to 21 (which is 4), and the result of the second formula is subtracted from the first formula (7-4) to get the count of numbers between two numbers (3).

If you want to count cells with a number is greater than 10 and less than 21 in the range B2:B14, including numbers 10 and 21, use this formula:

=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")

COUNTIFS to Count Numbers Between 2 Numbers

To count numbers between 10 and 21 (excluding 10 and 21) are contained in cells B2 through B9, use this formula:

=COUNTIFS(B2:B14,">10",B2:B14,"<21")

To include 10 and 21 in the count, just use ‘greater than or equal to’ (>=) instead of ‘greater than’ and ‘less than or equal to’ (<=) instead of ‘less than’ operators in the formulas.

COUNTIFS to Count Cells with Multiple Criteria (AND Criteria)

The COUNTIFS function is the plural counterpart of the COUNTIF function that counts cells based on two or more criteria in the same or multiple ranges. It is known as ‘AND logic’ because the function is made for counting cells only when all of the given conditions are TRUE.

For example, we want to find out how many times (count of cells) that bread (value in column A) has been sold less than 5 (value in column C).

We can use this formula:

=COUNTIFS(A2:A14,"Bread",C2:C14,"<5")

COUNTIF to Count Cells with Multiple Criteria (OR Criteria)

If you want to count the number of cells that meet multiple criteria in the same range, join two or more COUNTIF functions together. For example, if you want to find out how many times ‘Bread’ or ‘Cheese’ are repeated in the specified range (A2:A14), use the below formula:

=COUNTIF(A2:A14,"Bread")+COUNTIF(A2:A14,"Cheese")

This formula counts cells for which at least one of the conditions is TRUE. That’s why it’s called ‘OR logic’.

If you wish to evaluate more than one criteria in each of the functions, it’s better to use COUNTIFS instead of COUNTIF. In the example below, we want to get the count of “Ordered” and “Delivered” status for ‘Bread’, so we would use this formula:

=COUNTIFS(A2:A14,"Bread",C2:C14,"Ordered")+COUNTIFS(A2:A14,"Bread",C2:C14,"Delivered")

We hope this easy, but rather a long tutorial will give you some idea about how to use COUNTIF and COUNTIF functions in Excel.