How to Use COUNTIF Function

To count the number of cells that contain a particular value (e.g., a word or a number), you can use the COUNTIF function, which will help you get the number of entries with a specific value in a range.

Formula:

= COUNTIF(Range, Criterion)

Explanations:

– The range is required, which is the range of cells that you need to count;

– The criterion is required, which can be a number, a word, or cell reference.

Cautions:

The COUNTIF function can count the range of cells with a single criterion. You can use the COUNTIFS function when you have multiple conditions.

Example 1 (number value): To count how many persons having salary less than $100,000

Salary numbers are in column C, so to count the number of cells less than "$100,000" in column C.

– Range: Column C (C2:C9)

– Criteria: Less than $100,000

= Countif(C2:C9, "<100000")
There are 7 people having salary less than $100,000.

Example 2 (text value): To count how many persons' first name with the letters "en"

First names are located in column A, so we need to count how many cells with "en" in column A.

– Range: Column A (A2:A9)

– Criteria: Text "en"

= Countif(A2:A9, "*en*")There are 2 people's first name contain the letters "en".

Please note that (1) you will need the quotation marks for the text string, otherwise, Excel will consider the text value as a number and fail to get you the correct answers.

(2) The string is not case sensitive, where "EN" and "en" will match the same cells.

Example 3 (cell reference): To count how many persons having the same last names as the value in cell B5

– Range: Column B (B2:B9)

– Criteria: Cell reference value in cell B5

= Countif(B2:B9, B5)
There are 2 people having the same last names as the value in cell B5.

Download COUNTIF Function

Leave a Reply