How to Use Countif Function in Excel

Best Excel Tutorial

11 years ago

In this lesson, you learn about the COUNTIF function in Excel.

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

Table of Contents

Toggle

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 business 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”)

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)

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)

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)

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 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)

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*”)

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*”)

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”)

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”)

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,”*”)

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,”<>”&”*”)