Google Sheets COUNTIFS: How to Match Two Criteria Correctly

To count rows in Google Sheets that meet two conditions, use COUNTIFS with a range-and-condition pair for each condition. For example, with regions in A2:A8 and statuses in B2:B8, =COUNTIFS(A2:A8,"East",B2:B8,"Paid") counts rows where the region is East and the status is Paid.

What COUNTIFS Does

COUNTIFS counts records only when every condition you provide is true for the same row. That makes it useful for questions such as “How many East orders are paid?” or “How many support tickets are both high priority and unresolved?” It returns a number; it does not filter, list, or total the matching records.

In Google Sheets, the function follows this pattern: COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]). The square brackets in function documentation indicate optional arguments; do not type the brackets into the formula. Each range must be followed by its matching criterion, and each additional criterion requires another range-and-criterion pair.

Although the function name ends in “S,” using two conditions is a common use. You can add more pairs when you need three or more requirements, such as region, payment status, and a date window. Google Sheets evaluates the criteria together and counts matching rows, not the individual cells that happen to satisfy one condition.

A Two-Criteria Example

Suppose a small order list has a region in column A and a status in column B:

Row 2: East, Paid
Row 3: West, Paid
Row 4: East, Open
Rows 5, 7, and 8: East, Paid
Row 6: West, Open

With regions in A2:A8 and statuses in B2:B8, this formula counts orders that are both East and Paid:

=COUNTIFS(A2:A8,"East",B2:B8,"Paid")

The result is 4: rows 2, 5, 7, and 8 meet both conditions. Row 3 is paid but is not East; row 4 is East but is not paid. Neither row qualifies. This is the key idea: the conditions work like AND, not OR.

The ranges do not need to be adjacent. You could use a region column on one part of a sheet and a status column elsewhere, provided the ranges line up row by row and have matching dimensions. For a tidy data table, using the same start and end rows is usually easiest to audit.

How Criteria Are Matched

Each criterion can be a literal value, text, a number, a comparison, or a reference to a cell. Text criteria are usually written in double quotes. A criterion that refers to a cell is written as a cell reference without quotes. COUNTIFS text matching is not case-sensitive, so East and EAST are treated as the same text for this kind of count.

For example, with regions in A2:A8, statuses in B2:B8, the requested region in D2, and the requested status in E2, use:

=COUNTIFS(A2:A8,D2,B2:B8,E2)

If D2 contains East and E2 contains Paid, the result is 4 for the sample order list. Referencing cells makes a summary sheet easier to reuse: change the values in D2 or E2 to count a different region or status without editing the formula.

Criteria can also combine an operator with a cell value. Put the operator inside quotes and join it to the cell reference with the ampersand character. For example, the pattern ">="&F1 means “greater than or equal to the value in F1.” This is especially useful for thresholds and date ranges.

Using Number and Date Conditions

Comparison operators include > (greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), = (equal to), and <> (not equal to). For a quoted text criterion, keep the operator and value together, such as ">=100". When the value comes from a cell, concatenate the operator and reference instead.

Dates are most reliable when stored as actual spreadsheet date values, rather than text that merely looks like a date. Suppose G2:G6 contains January 4, January 31, February 1, February 15, and December 31, 2025; F1 is January 1, 2025; and F2 is February 1, 2025. To count dates in January, including January 1 but excluding February 1, use:

=COUNTIFS(G2:G6,">="&F1,G2:G6,"<"&F2)

The result is 2, because January 4 and January 31 fall within that half-open interval. The same date range appears twice because one condition sets the start and the other sets the cutoff. Using a less-than next-period date avoids ambiguity about the final day, particularly when source values might include times as well as dates.

Wildcards and Partial Text

For text criteria, Google Sheets supports wildcards in COUNTIFS criteria. An asterisk (*) matches any number of characters, including none; a question mark (?) matches one character. These are handy when entries share a beginning or contain a variable part. A tilde (~) can be placed before a wildcard character when you want to match that character literally rather than use it as a wildcard.

Suppose C2:C6 contains North depot, North route, South depot, North, and North-west, while D2:D6 contains Open, Paid, Open, Open, and Paid. To count entries whose location begins with North and whose status is Open, use:

=COUNTIFS(C2:C6,"North*",D2:D6,"Open")

The result is 2: North depot and North meet both conditions. North route and North-west begin with North but have Paid status, so they are excluded. A wildcard does not change the AND behavior; it only broadens which text values satisfy one criterion.

Two Criteria Versus an OR Condition

A frequent source of confusion is whether two criteria mean “both” or “either.” In COUNTIFS, separate criteria pairs are combined as AND: the same record has to satisfy each pair. If you need East or West orders that are Paid, you need to express the alternatives rather than add East and West as separate pairs.

With regions in A2:A8 and statuses in B2:B8 in the sample list, you can add the two separate counts:

=COUNTIFS(A2:A8,"East",B2:B8,"Paid")+COUNTIFS(A2:A8,"West",B2:B8,"Paid")

The result is 5: four East paid orders plus one West paid order. This addition works because each order can belong to only one of those two regions, so no row is counted twice. When OR alternatives can overlap, adding counts may double-count records; account for the overlap or restructure the criteria before relying on the total.

Common Errors and Ways to Check

If a formula returns zero unexpectedly, first inspect the underlying cells rather than only the displayed formatting. A date stored as text may not compare like a real date, and a number stored as text may not behave like a numeric value. Similarly, an extra space in a status such as “Paid ” can prevent an exact text match even though the cell looks nearly identical.

Check that every range covers the intended rows and that the ranges have the same height and width. Mismatched range sizes can cause an error or an unintended count.
Confirm that each criterion follows the correct range. Swapping a criterion with its range can produce a valid-looking formula that counts the wrong thing.
Check spelling, spaces, and data types. If needed, clean inconsistent source data rather than hiding the problem in a complicated criterion.
Review comparison operators and quotation marks. A literal text criterion needs quotes; a cell reference should generally not be quoted.
Test each condition separately with a small, known set of rows. Then combine the pairs and verify a few qualifying and nonqualifying records manually.

Blank criteria deserve care, too. A criterion of "" can be used to count blank cells in a range, while "<>" is commonly used to count nonblank cells. If a formula needs to treat genuinely empty cells differently from formulas that return an empty string, inspect the source cells and test the behavior on representative rows rather than assuming the display tells the whole story.

COUNTIFS Compared With COUNTIF

Use COUNTIF when a count depends on one condition, such as the number of Paid records in a status column. Use COUNTIFS when two or more conditions must all apply, such as Paid status and East region. The functions are closely related, but COUNTIFS keeps multiple tests in one formula and makes the row-by-row relationship clear.

Google Sheets and Microsoft Excel both provide COUNTIFS, and the basic syntax shown here also works in current Excel versions. One practical difference is that spreadsheet locales can use different formula argument separators: many use commas, while some use semicolons. If a formula copied from an example is rejected, check the spreadsheet’s locale and use the separator expected by that locale. In Sheets, formulas are entered directly in a cell in the browser; there is no special menu command required to use COUNTIFS.

Frequently Asked Questions

Does COUNTIFS use AND or OR?

Separate criteria pairs in one COUNTIFS formula use AND logic: a row must meet all the conditions to count. For OR alternatives, combine separate counts when the alternatives do not overlap, or otherwise handle duplicate matches explicitly.

Can COUNTIFS check two conditions in the same column?

Yes. You can repeat the same range in two criteria pairs, such as using one condition for a lower date limit and another for an upper limit. The range and criteria still need to be supplied as separate pairs.

Can I use cell references as the criteria?

Yes. Put the reference in the criterion position without quotation marks. For a comparison based on a cell, join the operator in quotes to the reference with an ampersand, as in ">="&F1.

Why does COUNTIFS return zero when the values look identical?

Common causes include leading or trailing spaces, text that looks like a number or date, a misspelled criterion, or a criterion range that does not cover the matching rows. Check the stored values and ranges, not only how cells appear on screen.

Can I count partial text with two criteria?

Yes. Use a wildcard in one text criterion and another condition in a second range-and-criterion pair. For example, "North*" matches text beginning with North while the other pair can restrict the count to a status such as Open.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.