Excel COUNTIF and COUNTIFS Functions

last modified October 5, 2026

The COUNTIF and COUNTIFS functions count the cells that satisfy one or more conditions. COUNTIF applies a single criterion, while COUNTIFS applies several criteria at once using AND logic. This tutorial covers their syntax, comparison operators, wildcards, dates, blank and non-blank counting, cell reference criteria, and the errors that appear when the arguments are malformed.

COUNTIF/COUNTIFS Function Basics

COUNTIF counts the cells in one range that meet one condition. COUNTIFS extends this by testing pairs of ranges and criteria, and a cell is counted only when every pair matches. Text criteria are not case-sensitive.

Function	Description	Syntax
COUNTIF	Counts cells that meet one condition	=COUNTIF(range, criteria)
COUNTIFS	Counts cells that meet every condition (AND logic)	=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

This table compares the two functions. COUNTIF takes one range and one criterion, while COUNTIFS takes one or more range/criteria pairs. Both accept numbers, text, comparisons, cell references, and wildcards as criteria.

Argument	Meaning
range / criteria_range	The cells tested against the criteria. Every criteria range in a `COUNTIFS` call must have the same number of rows and columns.
criteria	The condition to satisfy. It can be a number (`100`), text (`"Apple"`), a comparison (`">100"`), a wildcard pattern (`"App*"`), or a reference joined to an operator (`">"&D1`). Text criteria are not case-sensitive and must be quoted.

Criteria are evaluated one cell at a time. If the first cells of every range meet their criteria, the count increases by one; then Excel moves to the second cells, and so on. Ranges may be adjacent or separate, but they must be the same shape. A COUNTIFS call accepts up to 127 range/criteria pairs.

Basic COUNTIF Example

This example demonstrates counting cells that contain a specific value.

A	B
Apple
Orange
Apple
Banana
	=COUNTIF(A1:A4, "Apple")

Basic COUNTIF formula

=COUNTIF(A1:A4, "Apple")

This formula counts how many times "Apple" appears in cells A1 through A4. The result is 2. The criterion can be a value, a cell reference, or an expression. Text criteria must be enclosed in quotes.

COUNTIF with Comparison Operators

COUNTIF supports the comparison operators >, <, >=, <=, and <>. This example counts numbers greater than a threshold.

A	B
15
25
10
30
	=COUNTIF(A1:A4, ">20")

COUNTIF with comparison operator

=COUNTIF(A1:A4, ">20")

This formula counts how many numbers in A1:A4 are greater than 20. The result is 2 (25 and 30). The operator and the value are written together inside one quoted string. To compare against a cell instead of a literal, join the operator to the reference with &, for example =COUNTIF(A1:A4, ">"&D1). If D1 contains 20, this produces the same result as the formula above.

COUNTIF with Wildcards

COUNTIF supports wildcards for partial text matching. The asterisk (*) matches any sequence of characters, while the question mark (?) matches any single character. Wildcards work only with text criteria, not with numbers.

A	B
Apple
Application
Banana
App
	=COUNTIF(A1:A4, "App*")

COUNTIF with wildcard

=COUNTIF(A1:A4, "App*")

This formula counts cells starting with "App". The result is 3 (Apple, Application, App). Wildcards are powerful for text pattern matching. To match an actual asterisk or question mark, precede it with a tilde (~), for example =COUNTIF(A1:A4, "App~*").

COUNTIF with Dates

Dates in Excel are stored as serial numbers, so they can be compared like any other number. This example counts dates after a given day.

A	B
1/15/2023
2/20/2023
1/10/2023
3/5/2023
	=COUNTIF(A1:A4, ">"&DATE(2023,1,31))

COUNTIF with date criteria

=COUNTIF(A1:A4, ">"&DATE(2023,1,31))

This formula counts dates after January 31, 2023. The result is 2 (2/20/2023 and 3/5/2023). Building the criterion with the DATE function returns a real date serial, which is safer than typing a date inside the criteria string because it is not affected by the regional date format.

Basic COUNTIFS Example

COUNTIFS extends COUNTIF by supporting multiple criteria. This example counts rows meeting two conditions.

A	B	C
Apple	Red
Apple	Green
Banana	Yellow
Apple	Red
		=COUNTIFS(A1:A4, "Apple", B1:B4, "Red")

Basic COUNTIFS formula

=COUNTIFS(A1:A4, "Apple", B1:B4, "Red")

This formula counts rows where column A is "Apple" AND column B is "Red". The result is 2. Every criterion must be met at the same time. Each additional condition follows the same range/criteria pattern.

COUNTIFS with Multiple Criteria Types

COUNTIFS can combine different types of criteria in one formula. This example uses text, number, and date criteria together.

A	B	C	D
North	500	1/5/2023
South	750	2/10/2023
North	300	1/20/2023
East	900	3/15/2023
			=COUNTIFS(A1:A4, "North", B1:B4, ">400", C1:C4, ">="&DATE(2023,1,1))

COUNTIFS with mixed criteria

=COUNTIFS(A1:A4, "North", B1:B4, ">400", C1:C4, ">="&DATE(2023,1,1))

This formula counts rows where the region is "North", sales are greater than 400, and the date is on or after 1/1/2023. The result is 1 (only the first row). COUNTIFS is powerful for complex multi-condition counting scenarios.

COUNTIFS with OR Logic

COUNTIFS uses AND logic across its pairs. To get OR logic, add several COUNTIFS results together. This example counts rows meeting either of two conditions.

A	B	C
Apple	Red
Banana	Yellow
Grape	Purple
Apple	Green
		=COUNTIFS(A1:A4, "Apple") + COUNTIFS(A1:A4, "Banana")

OR logic with COUNTIFS

=COUNTIFS(A1:A4, "Apple") + COUNTIFS(A1:A4, "Banana")

This formula counts fruits that are either Apples OR Bananas. The result is 3. For more complex OR conditions, consider using SUMPRODUCT with multiple conditions instead.

COUNTIF with Cell Reference Criteria

Instead of hardcoding criteria, you can reference other cells. This makes formulas dynamic and easier to modify.

A	B	C
Apple	Apple
Orange
Apple
Banana
		=COUNTIF(A1:A4, B1)

COUNTIF with cell reference

=COUNTIF(A1:A4, B1)

This formula counts occurrences of the value in B1 ("Apple") within A1:A4. The result is 2. Using a cell reference makes the formula adaptable to changing requirements without editing the formula itself. An empty referenced cell is treated as the value 0.

COUNTIF/COUNTIFS with Blank or Non-Blank Cells

These functions can count blank or non-blank cells using specific criteria syntax.

A	B
Apple

Orange

	=COUNTIF(A1:A4, "")

COUNTIF for blank cells

=COUNTIF(A1:A4, "")

This formula counts blank cells in A1:A4. The result is 2. A criterion of "" matches truly empty cells and also cells that hold empty text returned by a formula, so it agrees with what COUNTBLANK reports. To count non-blank cells instead, use the criterion "<>", which counts every cell that is not blank. These techniques are useful for data completeness analysis.

COUNTIFS Ranges Must Match in Size

Every criteria range in COUNTIFS must have the same number of rows and columns. If the ranges differ in shape, the formula returns #VALUE! instead of a count.

A	B
Apple	Red
Apple	Green
Banana	Yellow
	=COUNTIFS(A1:A3, "Apple", B1:B2, "Red")

Mismatched range sizes

=COUNTIFS(A1:A3, "Apple", B1:B2, "Red")

A1:A3 spans three rows while B1:B2 spans only two, so the formula returns #VALUE!. Always build the criteria ranges over the same rows and columns. COUNTIF does not have this restriction because it accepts only one range.

COUNTIF/COUNTIFS Performance Tips

When working with large datasets, these tips can improve performance:

Use specific ranges instead of entire columns (e.g., A1:A100 vs A:A).
Avoid volatile functions like TODAY() in criteria if possible.
Combine with Excel Tables for structured references and efficiency.
Use helper columns to simplify complex criteria when practical.

Common Use Cases

COUNTIF/COUNTIFS are versatile for many scenarios:

Counting sales above a target in specific regions.
Tracking inventory items by category and status.
Analyzing survey responses with multiple conditions.
Monitoring project tasks by completion dates.

COUNTIF/COUNTIFS Errors and Troubleshooting

Cause	Symptom	Fix
A text or comparison criterion is not quoted	A wrong count or 0	Enclose text and comparison criteria in quotes, for example `">400"`
The operator and the value are split into separate arguments	#VALUE! error or no match	Write the operator and value together in one quoted string, for example `">400"`
A cell reference is not joined to its operator	Wrong count or no match	Concatenate with `&`, for example `">"&D1`
The `COUNTIFS` ranges have different sizes	#VALUE! error	Make every criteria range the same number of rows and columns
Wildcards are used on numeric data	No match	Wildcards match text only; compare numbers with operators instead
The criteria string is longer than 255 characters	An incorrect count	Split the string and join the parts with `&`, or use `SUMPRODUCT` with `EXACT`
Data contains extra spaces or curly quotes	An unexpected count	Clean the data with `TRIM` and `CLEAN`
The formula refers to a closed workbook	#VALUE! error	Open the source workbook before recalculating

The COUNTIF and COUNTIFS functions are essential for counting data based on conditions in Excel. From simple tallies to complex multi-criteria analysis, they offer powerful functionality. Remember that COUNTIFS uses AND logic across its range/criteria pairs, that text criteria are not case-sensitive, and that wildcards work only on text. Quote comparison criteria, join operators to cell references with &, build date criteria with DATE, and keep every COUNTIFS range the same size. Mastering these functions will enhance your data analysis capabilities significantly.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.

Ebooks