How to Use Countif Function in Excel

In this lesson, you will learn about the COUNTIF function.

A COUNTIF function counts the number of times within the specified range of cells that the specified value is performed as a criterion.

Countif function syntax

=COUNTIF (range, criteria)

  • Range - the range of cells that you want to count the data. There has to be an ongoing area.
  • Criteria - a condition that must be met for cell counting. In the case of numeric values, the condition can look like e.g. 100.

Countif function examples

Let's analyze bisuness examples of how to use countif function in Excel.

Count string with exact string of text

You want to count cells where there is only REPORT text. The cells are in the C column.

The formula is =COUNTIF(C2:C10,"REPORT")

COUNTIF exact string of text

Greater than

You want to count cells whose value is greater than 10. The cells are in the C column.

The formula is =COUNTIF(C2:C10,">"&10)

COUNTIF greater than

Greater than or equal to

You want to count cells whose values are greater than or equal to 10. The cells are in the C column.

The formula is =COUNTIF(C2:C10,">="&10)

COUNTIF greater equal than

Not equal to

You want to count cells where the value is not equal to 10. The cells are in the C column.

The formula is =COUNTIF(C2:C10,"<>"&10)

COUNTIF not equal

Less than x but greater than y

You want to count cells where value is less than 40 but more than 15. The cells are in the C column.

The formula is =COUNTIF(C2:C10,"<"&40)-COUNTIF(C2:C10,"<="&15)

COUNTIF less more

Countif plus countif

You want to count cells whose value is greater than 40 plus less than 15. The cells are in the C column.

The formula is =COUNTIF(C2:C10,">"&40)+COUNTIF(C2:C10,"<"&15)

COUNTIF less plus more

Count cells containing a string of text.

You want to count cells that contain REPORT text. The cells are in the C column. Use an asterisk in your formula. An asterisk replaces any other signs.

The formula is =COUNTIF(C2:C10,"*REPORT*")

 COUNTIF contains string of text

Count cells beginning with a string of text.

You want to count cells that begin to REPORT text. The cells are in the C column. Use an asterisk in your formula. An asterisk replaces any other signs.

The formula is =COUNTIF(C2:C10,"REPORT*")

COUNTIF begin string of text

Count cells that end in a string of text.

You want to count cells that end in RT text. The cells are in the C column. Use an asterisk in your formula. An asterisk replaces any other signs.

The formula is =COUNTIF(C2:C10,"*RT")

COUNTIF end string of text

Count the cells that end in a string of text that contains 6 letters.

You want to count cells that end in RT text and contain 6 letters. The cells are in the C column. Use a question mark in your formula. A question mark replaces one sign.

The formula is =COUNTIF(C2:C10,"????RT")

COUNTIF end string of text

Count the cells containing any text.

You want to count cells that contain any text. The cells are in the C column. Use an asterisk in your formula. An asterisk replaces any other signs.

The formula is =COUNTIF(C2:C10,"*")

COUNTIF contains text

Count the cells that do not contain any text.

You want to count cells that do not contain any text. The cells are in the C column. Use an asterisk in your formula. An asterisk replaces any other signs. <> means not equal to.

The formula is =COUNTIF(C2:C10,"<>"&"*")

COUNTIF not contain text

Greater than or equal to the value of the cell.

You want to count cells whose value is greater than or equal to the value of cell C12. The cells are in the C column.

The formula is =COUNTIF(C2:C10,">="&C12)

COUNTIF greater equal than cell value

Greater than the value returned by the cell

You want to count cells whose value is greater than the value of the cell C12. The cells are in the C column. 

The formula is =COUNTIF(C2:C10,">"&C12)

COUNTIF greater than cell value

Now you know how to use the COUNTIF function in Excel. You also know many COUNTIF formulas to use in your business cases.

Further reading: 
Countifs function 
COUNTIF for non-empty cells 
How to countif cells with a partial match