COUNTIFS Function in Google Sheets
Counts cells that meet multiple conditions across multiple ranges. The multi-criteria version of COUNTIF, used for filtered counts and category breakdowns.
Syntax
COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])Returns: The number of rows where ALL criteria are met.
Excel equivalent: COUNTIFS (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| criteria_range1 | Required | The first range to test. |
| criterion1 | Required | The first condition. |
| criteria_range2, criterion2, ... | Optional | Additional range/criterion pairs. All conditions must be TRUE for a row to be counted. |
Examples
Closed deals over $10k
=COUNTIFS(A:A, "Closed Won", B:B, ">10000")Counts rows where status is "Closed Won" AND amount is greater than $10,000.
Active customers in a date window
=COUNTIFS(C:C, "Active", D:D, ">="&DATE(2026,1,1), D:D, "<"&DATE(2026,7,1))Counts active customers added between Jan 1 and Jun 30 2026. Same range D:D appears twice with different date bounds.
Categories with exclusions
=COUNTIFS(A:A, "<>Tax", A:A, "<>Fee", B:B, "Paid")Counts paid items that are neither Tax nor Fee. <> is the not-equal operator.
When to use an alternative
- QUERY — You want counts grouped by multiple dimensions at once.
- FILTER + COUNTA — Your conditions are dynamic computed arrays.
- Pivot Table — Exploring data with interactive slicing.
Common errors and how to fix them
#VALUE!
Cause: criteria_ranges have different dimensions.
Fix: All ranges must be the same size — COUNTIFS aligns them row-by-row.
Returns 0 unexpectedly
Cause: One criterion is too restrictive.
Fix: Test each criterion alone with COUNTIF to find which one filters everything out.
Related functions
Frequently Asked Questions
Can COUNTIFS use OR logic?
No — all criteria are AND. For OR, either sum two COUNTIFS results, or use SUMPRODUCT with explicit boolean math: SUMPRODUCT((A:A="X")+(A:A="Y")) for the count of X-or-Y rows.
What if I want to count distinct values?
COUNTIFS doesn't dedupe. For unique counts use COUNTUNIQUE for a single column, or COUNTUNIQUEIFS for unique values matching criteria, or wrap with UNIQUE in an array formula.