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.