How to Use the COUNTIF Function in a Spreadsheet

What is the COUNTIF function?

The COUNTIF function in a spreadsheet counts the number of cells in a range that meet a specific criterion or condition. You can use COUNTIF to count cells containing numbers, dates, text, characters, or blanks.

Syntax of the COUNTIF function: =COUNTIF(range, criteria)

• Range: The group of cells to count.

• Criteria: The condition that determines which cells will be counted. It can be a number, character, text string, cell reference, or expression.

Note: COUNTIF uses a single criterion. If you need to use multiple criteria, you can use the COUNTIFS function.

Examples of how to use COUNTIF in a spreadsheet

Example 1: COUNTIF greater than, equal to, or less than

Suppose you have a list of sales figures in cells C2 to C12, and you want to count how many times the sales exceed 10,000.

Enter the formula =COUNTIF(B2:B12,">10,000") and you’ll get a result of 7.

Pro Tip: Comparing to a Cell Reference

When comparing to a cell reference, enclose the operator in quotes and add an ampersand (&) before the cell reference. For instance, to count how many sales exceed a goal value stored in cell B15, use this formula: =COUNTIF(B2:B12,">"&B15). You’ll get a result of 4.

Example 2: COUNTIF for an exact-match text string

Following the above question, we then want to count the number of deals closed by William. We have a list of names in cells A2 through A12, and we want to count how many times the name "William" appears in this list.

Use the formula =COUNTIF(A2:A12,"william"). It counts the number of cells in the range A2:A12 that exactly match the text string "william". You’ll get a result of 3.

Note: The COUNTIF function is not case-sensitive, meaning "william" and "WILLIAM" are considered the same.

Example 3: COUNTIF for text string using wildcard characters

Wildcards allow for flexible matching within text values. You can use a wildcard character in your criteria to count the cells containing a specific word, phrase, or letter.

Note: Wildcards work only with text values and do not apply to numbers.

Asterisk (*)

The asterisk (*) matches any sequence of text characters. This is particularly useful when you want to count cells containing a certain word or phrase, regardless of its position within the cell.

Example: Suppose you have a dataset of customer feedback comments, and you want to count how many comments mention the word "dissatisfied." You can use the formula:

=COUNTIF(A2:A7,"*dissatisfied*")

This formula counts all the cells in the range A2:A7 that contain the word "dissatisfied" anywhere in the cell. The asterisks allow for any sequence of text characters before or after the word "dissatisfied." You’ll get a result of 2.

Pro Tip: Counting Cells Starting or Ending with Text

If you want to count cells that start or end with certain text no matter how many other characters a cell contains, use formulas like:
=COUNTIF(Range,"Pro*") to count cells that begin with "Pro",=COUNTIF(Range,"*ing") to count cells that end with "ing".

Question Mark (?)

The question mark (?) matches any single character, including spaces. It's handy for counting cells with an exact number of characters.

Example: In a retail inventory scenario, imagine you're seeking product codes of seven characters, with the last two indicating "BL" for blue color. Utilize the formula =COUNTIF(A2:A11, "?????BL") to efficiently manage inventory based on color variations. In this case, you’ll get a result of 4.

Pro Tips: To count cells containing a “literal” question mark (?) or asterisk (*), use a tilde (~) before the wildcard character in the formula to escape. For example, =COUNTIF(range,"*~?*") will count all cells containing the question mark in the range.

Example 4: COUNTIF for blank and non-blank cells

You can use the COUNTIF function in a spreadsheet to count the number of empty or non-empty cells in a specified range.

COUNTIF for non-blank cells

Use the syntax: =COUNTIF(range,"<>")

The "<>" operator means "not equal to" in a spreadsheet, so this formula means count cells not equal to nothing. It counts all non-empty cells that contain any values regardless of their contents – whether numbers, text, or dates – in the specified range.

Pro Tip: To exclusively count non-blank cells containing text values, including empty strings, utilize =COUNTIF(range,"*"). This ensures cells with values like numbers or dates are excluded from the count.

COUNTIF for blank cells

Conversely, to count all empty cells within a range, you can use the syntax: =COUNTIF(range,"")

Note: The COUNTBLANK function can also serve this purpose. Simply employ =COUNTBLANK(range) to achieve the same result as =COUNTIF(range,"").

However, both COUNTIF and COUNTBLANK functions include empty strings ("") returned by formulas as non-blank. This can lead to issues when counting formula results. To exclude empty strings from the count, use =COUNTIF(range, "<>") - COUNTIF(range, ""). Alternatively, consider using other functions like COUNTA or SUMPRODUCT based on your requirements.

Example 5: COUNTIF for dates

You can also use COUNTIF to analyze date data, enabling you to efficiently track and manage date-related information.

Here are some useful formulas:

• Count dates equal to a specified date: =COUNTIF(range,"3/1/2024")

• Count dates greater than or equal to a specified date: =COUNTIF(range,">=3/1/2024")

• Count dates equal to the current date: =COUNTIF(range,TODAY())

• Count dates prior to the current date: =COUNTIF(range,"<"&TODAY())

• Count dates due in a week: =COUNTIF(range,"="&TODAY()+7)

• Count dates in a specific date range: =COUNTIF(range, ">=3/1/2024")-COUNTIF(range, ">6/30/2024")

COUNTIF with multiple criteria

COUNTIF is tailored for single criterion assessment. You have to use the COUNTIFS function to count cells that match two or more criteria. However, you can still simply achieve multi-criteria counting by combining multiple COUNTIF functions in one formula.

For example, to count values between two numbers in a range, such as those greater than 10 but less than 50, you can use the formula =COUNTIF(range,">10")-COUNTIF(range,">=50"). Additionally, to count cells with multiple OR criteria, like counting occurrences of "London" and "Manchester" in a tourist destination list, you can use =COUNTIF(range,"London")+COUNTIF(range,"Manchester").

Best practices of COUNTIF

1. Use Named Ranges: Instead of manually selecting ranges each time, consider using named ranges. This makes formulas more readable and reduces errors. Named ranges can be from the current worksheet, another worksheet in the same workbook, or from a different workbook. Ensure the referenced workbook is open.

Pro Tip: How to create a named range

• Method 1 (Fastest):

(1) Select the cell(s).
(2) Type a name into the Name Box.
(3) Press "Enter".

• Method 2:

(1) Select the cell(s).
(2) Go to the "Formula" tab, select "Name" and then "Define Name".
(3) In the New Name dialog box, set up the range name, scope, and reference and add comments for detailed explanations.
(4) Click "OK."

2. Ensure Absolute References: When copying the COUNTIF formula to other cells, ensure the range reference is absolute (e.g., $A$1:$A$10) to avoid it changing and causing incorrect counts.

3. Properly Format Criteria: Enclose the criteria argument in quotes and employ the appropriate wildcards to avoid COUNTIF errors.

4. Test with Sample Data: Before applying COUNTIF to a large dataset, test it with sample data to validate its functionality and ensure it produces the desired results. This practice helps catch any potential issues early on.

5. Document Your Formulas: Documenting your formulas, particularly when dealing with complex criteria or extensive datasets, aids in understanding the logic behind the COUNTIF function. It also facilitates troubleshooting should any problems arise, making it easier for you and others to maintain and debug the spreadsheet.