How to Use COUNTIFS in Google Sheets: Comprehensive Guide

COUNTIFS counts the rows that meet several conditions at once. It is the multi-criteria version of COUNTIF: how many open tickets are assigned to Ana, how many orders over 500 shipped in March, how many students scored above 80 in both exams.

Syntax

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

criteria_range1: The cells to test against the first criterion.
criterion1: The first condition.
criteria_range2, criterion2, ... [Optional]: More range/condition pairs.

A row is counted only if it meets all the conditions (AND logic). All the ranges must have the same size.

Basic example

With status in A2:A100, owner in B2:B100 and amount in C2:C100:

=COUNTIFS(A2:A100, "Open", B2:B100, "Ana")

Counts the rows where the status is Open and the owner is Ana.

=COUNTIFS(A2:A100, "Open", C2:C100, ">500")

Counts open items over 500.

Writing criteria

Criteria follow the same rules as COUNTIF:

Exact: "Open" or a cell such as E1
Comparison: ">500", "<=50", "<>Closed"
Wildcards: "App*", "*corp", "?????"
Blank / not blank: "=" and "<>"
Operator plus cell: ">=" & F1

Counting within a date range

Use two conditions on the same date column, one for each end:

=COUNTIFS(D2:D100, ">=" & DATE(2026, 3, 1), D2:D100, "<=" & DATE(2026, 3, 31))

With start and end dates in cells:

=COUNTIFS(D2:D100, ">=" & F1, D2:D100, "<=" & G1)

Count by month, with the first day of each month in F2:

=COUNTIFS(D2:D100, ">=" & F2, D2:D100, "<" & EDATE(F2, 1))

Counting between two numbers

=COUNTIFS(B2:B100, ">=60", B2:B100, "<80")

Counts the values from 60 up to, but not including, 80. Using the same range twice is the standard way to define a band.

OR logic

COUNTIFS combines conditions with AND. For OR on the same column, pass an array of values:

=SUM(COUNTIFS(A2:A100, {"Open", "Pending"}, B2:B100, "Ana"))

Counts Ana's rows that are Open or Pending.

Practical patterns

Two-way summary table

Statuses down column E and owners across row 1:

=COUNTIFS($A$2:$A$100, $E2, $B$2:$B$100, F$1)

Drag it right and down. The $ signs keep the ranges fixed while the labels shift.

Count duplicate rows

=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)

Returns how many times the combination of A and B appears. A value above 1 means a duplicate row.

Count with a dropdown that allows "All"

=COUNTIFS(A2:A100, IF(E1 = "All", "*", E1))

The wildcard * matches any text.

Percentage of rows meeting both conditions

=COUNTIFS(A2:A100, "Open", B2:B100, "Ana") / COUNTA(A2:A100)

Common errors

#VALUE!: "Mismatched range sizes". The ranges are not the same dimensions. Each range must have the same number of rows and columns.
Count is 0 but matches should exist. Look for trailing spaces, text-versus-number mismatches, or dates stored as text. Test each condition on its own with COUNTIF to find which one fails.
Operator without quotes. ">500" needs the quotes.
Text dates in criteria. ">=3/1/2026" can be read differently depending on locale. Use ">=" & DATE(2026, 3, 1).
Counting ranges of different sheets. Each range can be on another sheet, but all must be the same size.

COUNTIFS vs. COUNTIF vs. QUERY vs. pivot table

COUNTIF is simpler for a single condition. COUNTIFS handles several. For counts across many combinations, a pivot table or QUERY with group by is quicker to maintain than a grid of formulas.

Frequently asked questions

Is COUNTIFS case-sensitive? No.

How many conditions can I use? Many. Each is a range/criterion pair.

Can I count rows where one column equals another? Not directly with COUNTIFS. Use SUMPRODUCT(--(A2:A100 = B2:B100)).

Does it work with a single condition? Yes, it is identical to COUNTIF in that case.

Related Functions

COUNTIF: Count with a single condition.
SUMIFS: Sum with several conditions.
COUNT: Count numeric cells.
COUNTA: Count non-empty cells.
QUERY: Count and group data with SQL-like syntax.

COUNTIFS