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
- Excel Business Template for Profit and Loss
- Excel Business Template for Cash Flow: How to Build One
- Excel Bullet Journal Template: Setup & Free Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Scenario Manager Tutorial: Compare What-If PlansTo use Excel Scenario Manager, build a worksheet model, then select Data > What-If Analysis > Scenario Manager…
- Excel NOW Function Not Updating? Causes and FixesExcel’s NOW() function does update, but it does not run like a live clock: it returns the current…
- IF Then Formula for Dates in Excel: Examples and FixesUse Excel’s IF function to test a date and return one result when the condition is true and…
- Power Query Lookup: How to Match Data Across TablesIn Excel, a Power Query lookup is usually a merge: open the Power Query Editor, choose Home >…
- Excel Growth Rate Formula: Calculate Change Over TimeTo calculate growth rate in Excel, divide the change between the new and old values by the old…
- Excel PV Formula for Different PaymentsExcel’s PV function is designed for a fixed payment repeated over a set number of periods, so it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.