Excel COUNTIF Specific Cells: How to Count Only Certain Cells

Formulas & functions · Updated November 22, 2026

To count only specific cells in Excel, use the COUNTIF function with a range and a condition, like =COUNTIF(A2:A20,"Yes"), which counts how many cells in that range equal “Yes”. For counting cells that meet one of several criteria, use COUNTIFS, and for counting cells by color or font, use a different approach (see below). This article explains the syntax, gives examples, and covers common problems and alternatives.

What COUNTIF Does and Its Syntax

COUNTIF counts the number of cells in a range that meet a single condition. Its syntax is:

=COUNTIF(range, criteria)
  • range – the group of cells you want to count (e.g., A2:A100).
  • criteria – the condition that decides which cells to count. It can be a number, text, expression, or cell reference.

For example, with sales figures in B2:B20, =COUNTIF(B2:B20,">100") counts how many sales exceeded 100. The criteria is placed in double quotes when it contains a comparison operator or text. With a cell reference, you don’t use quotes: =COUNTIF(B2:B20,D1) counts cells equal to the value in D1.

Counting Cells That Match Text or Numbers Exactly

To count cells that exactly match a text string, put the text in quotes: =COUNTIF(A2:A20,"Pending"). Text matching is case-insensitive by default, so “pending” and “PENDING” both count. To count numbers, just type the number: =COUNTIF(C2:C20,42).

You can also use wildcards for partial matches:

  • "*" – any sequence of characters. =COUNTIF(A2:A20,"*apple*") counts cells containing “apple” anywhere.
  • "?" – any single character. =COUNTIF(A2:A20,"A?") counts two-character cells starting with A.
  • "~" – escapes wildcards. =COUNTIF(A2:A20,"~*") counts cells that literally contain an asterisk.

Using COUNTIFS for Multiple Conditions

When you need to count cells that meet multiple criteria, use COUNTIFS. Its syntax is =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). All conditions must be true for a cell to be counted.

Example: with names in A2:A20 and status in B2:B20, =COUNTIFS(A2:A20,"John",B2:B20,"Done") counts rows where name is John and status is Done.

Note: COUNTIFS uses AND logic. If you need OR logic (e.g., count cells that are “Red” or “Blue”), you can add two COUNTIF results: =COUNTIF(A2:A20,"Red")+COUNTIF(A2:A20,"Blue").

How to Choose Between COUNTIF, COUNTIFS, and Other Functions

Choose the right function based on your need:

  • COUNTIF – one condition, one range.
  • COUNTIFS – multiple conditions, possibly on different ranges.
  • SUMPRODUCT – for complex conditions, like counting cells based on another cell’s value in the same row. Example: =SUMPRODUCT((A2:A20="Yes")*(B2:B20="High")) counts rows where A is “Yes” and B is “High”.
  • COUNT – counts numeric cells only (ignores text).
  • COUNTA – counts non-empty cells.
  • COUNTBLANK – counts empty cells.

For counting cells by color, there is no built-in function. Use a filter or a VBA macro (see below).

Common Problems and How to Fix Them

Here are frequent issues and their solutions:

  • Criteria with quotes not working: If you type =COUNTIF(A2:A20,Yes), Excel treats “Yes” as a named range and returns an error. Always use quotes for text: "Yes".
  • Counting blank or non-blank cells: Use "" for blank: =COUNTIF(A2:A20,"") counts empty cells. Use "<>" for non-blank: =COUNTIF(A2:A20,"<>"). But note: "<>" counts cells that contain any text, even an empty string from a formula.
  • Leading/trailing spaces: Text with spaces won’t match. Use =COUNTIF(A2:A20,"* Yes *") or clean data with TRIM.
  • Numbers stored as text: If numbers are left-aligned, they may be text. Use =COUNTIF(A2:A20,"*"&D1&"*") or convert to numbers.
  • COUNTIFS with blank criteria: If you use a cell reference that is blank, COUNTIFS treats it as zero. To count blanks, use "".

Counting Cells by Color or Font

Excel does not have a built-in function to count cells by fill color or font color. The recommended approach is to use the Filter by Color feature: select your data, go to Data > Filter, click the dropdown arrow in the column header, choose Filter by Color, and select the color. The status bar shows the count of visible cells.

For a dynamic count, you can use a VBA macro. Here’s an example that counts cells with a specific fill color in a range:

Function CountByColor(rng As Range, colorCell As Range) As Long
    Dim cell As Range
    Dim targetColor As Long
    targetColor = colorCell.Interior.Color
    For Each cell In rng
        If cell.Interior.Color = targetColor Then
            CountByColor = CountByColor + 1
        End If
    Next cell
End Function

To use it, press Alt+F11 to open the VBA editor, insert a module, paste the code, and return to Excel. Then use =CountByColor(A2:A20,B1) where B1 has the color you want to count. Save a copy of your workbook before running macros, as macros cannot be undone with Ctrl+Z.

Alternatives to COUNTIF for Complex Scenarios

For advanced counting, consider these alternatives:

  • SUMPRODUCT – counts with multiple conditions across arrays without needing Ctrl+Shift+Enter. Example: =SUMPRODUCT((A2:A20="Yes")*(B2:B20="High")).
  • FILTER (Excel 2021/Microsoft 365) – returns an array of matching cells, then wrap with COUNTA: =COUNTA(FILTER(A2:A20, B2:B20="High")).
  • COUNTIF with wildcards – for partial text matches as described.
  • PivotTables – for quick counts by category without formulas.

Frequently Asked Questions

How do I count cells that contain specific text in Excel?

Use =COUNTIF(range,"*text*") to count cells that contain that text anywhere. For example, =COUNTIF(A2:A20,"*apple*") counts cells with “apple” anywhere in the text.

Can COUNTIF count cells based on another cell’s value?

Yes, you can use a cell reference as the criteria without quotes. For example, =COUNTIF(A2:A20,D1) counts cells in A2:A20 that equal the value in D1. For a condition like greater than a value in D1, use ">"&D1.

Why is my COUNTIF returning 0 when I know there are matches?

Common reasons: text has extra spaces, numbers are stored as text, or the criteria is not in the correct format (e.g., missing quotes). Check for leading/trailing spaces with =LEN(A2) and compare with =LEN(TRIM(A2)).

How do I count cells that are not blank?

Use =COUNTIF(range,"<>") to count non-blank cells. For example, =COUNTIF(A2:A20,"<>"). Alternatively, use =COUNTA(range) which counts non-empty cells, but it also counts cells with formulas that return an empty string.

Can I use COUNTIF on non-contiguous cells?

No, COUNTIF requires a single contiguous range. To count across multiple separate ranges, use multiple COUNTIF functions and add them: =COUNTIF(A2:A5,"Yes")+COUNTIF(C2:C5,"Yes").

What is the difference between COUNTIF and COUNTIFS?

COUNTIF applies one condition to one range. COUNTIFS applies multiple conditions, each with its own range, and counts only cells that meet all conditions. Use COUNTIFS when you need AND logic across multiple columns.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.