Quick Answer

COUNTIFS Function

Counts cells that meet all of the specified conditions.

Syntax

COUNTIFS

(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameters

Parameter	Description	Required
criteria_range1	The range to evaluate with the first criteria.	Required
criteria1	The condition that criteria_range1 must satisfy.	Required
criteria_range2, criteria2	Additional range/criteria pairs (optional).	Optional

Basic Example

Count orders from the East region

=COUNTIFS(A2:A100, "East")

ResultNumber of East orders

Counts how many cells in A2:A100 contain "East".

Advanced Examples

Example 1: Multiple criteria

Orders by region and status

Count orders where region is East and status is Completed

=COUNTIFS(A2:A100, "East", B2:B100, "Completed")

Result: Count of completed East orders

Only rows matching both criteria are counted.

Example 2: Numeric range

Sales values

Count sales between 1000 and 5000

=COUNTIFS(B2:B100, ">=1000", B2:B100, "<=5000")

Result: Count of values in range

Uses two criteria on the same numeric range.

How COUNTIFS Works

COUNTIFS iterates through each cell in the criteria ranges. It counts a row only if every criterion is satisfied. Empty cells are ignored unless the criteria explicitly matches blank.

1

Select criteria range

Choose the range for the first condition.

2

Enter criteria

Type the condition, e.g. "Completed" or ">50".

3

Add more pairs

Include additional range/criteria pairs for more conditions.

Important Notes & Limitations

All criteria ranges must be the same size and shape.
Criteria are combined with AND logic.
Counts cells, not numeric values; use SUMIFS for adding values.

Common Errors & Fixes

#VALUE! errorCriteria ranges have different sizes.

Fix: Make all ranges the same dimensions.

Unexpected countCriteria includes hidden spaces or wrong data type.

Fix: Use TRIM to clean text and check number formatting.

0 resultNo cells match all criteria.

Fix: Verify criteria and values.

Download Practice File

Practice COUNTIFS with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the COUNTIFS function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

AVERAGE

Calculates the mean of the provided numbers.

AVERAGEIF

Averages values that satisfy one condition.

AVERAGEIFS

Averages values that meet all of the specified conditions.

CORREL

Pearson correlation coefficient between two arrays.

COUNT

COUNT returns the count of cells containing numbers in a range.

COUNTA

COUNTA returns the count of all non-empty cells in a range, including text, numbers, and logical values.

Frequently Asked Questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF handles one condition. COUNTIFS handles multiple conditions.

Can COUNTIFS count blank cells?

Yes, use "" as the criteria to count empty cells.

Does COUNTIFS count text or numbers?

It counts cells that match the criteria, regardless of whether they contain text or numbers.

Can I use cell references as criteria?

Yes, use the cell reference directly without quotes.