fx
FunctionPedia
Quick Answer

COUNTIFS Function

Counts cells that meet all of the specified conditions.

✓ Excel✓ Google SheetsExcel Excel 2007+

Syntax

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

Parameters

ParameterDescriptionRequired
criteria_range1The range to evaluate with the first criteria.Required
criteria1The condition that criteria_range1 must satisfy.Required
criteria_range2, criteria2Additional 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 Tutorials

COUNTIF with Wildcard: Count Cells by Text Pattern

COUNTIF with wildcard patterns counts cells by prefix, suffix or length. Learn the * and ? rules, how to escape a literal asterisk, and where numbers fail.

Excel Sales Dashboard: Build One That Updates Itself

Build an Excel sales dashboard that keeps itself current: one flat sales table, SUMIFS KPIs, XLOOKUP targets and FILTER drill-downs that grow with the data.

SUMIFS Multiple Criteria

Build SUMIFS multiple criteria formulas that total values matching two or more conditions in Excel and Google Sheets, including dates, wildcards and OR logic.

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.