COUNTIFS Syntax in Google Sheets: Examples and Tips

Google Sheets uses =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) to count rows that meet two or more conditions at the same time. Each criterion range is paired with a criterion, and a row is counted only when every pair matches.

What COUNTIFS Does

COUNTIFS is useful when one condition is not enough to define the records you want to count. For example, you might count orders from a particular region only when their status is Paid, or count dates in a month only when the matching amounts exceed a threshold. It returns a number; it does not return the matching rows or add their values.

The function uses an AND relationship across its criteria pairs. If a row meets the first condition but fails the second, it is excluded. This is the central distinction to remember when building a formula: criteria added as additional range-and-criterion pairs narrow the count rather than broaden it.

For example, imagine a small table with sales representatives in A2:A7, regions in B2:B7, order statuses in C2:C7, and amounts in D2:D7. The six region/status combinations are North/Paid, South/Paid, North/Pending, North/Paid, South/Pending, and North/Paid. With that layout, this counts North-region orders that are Paid:

=COUNTIFS(B2:B7,"North",C2:C7,"Paid")

The result is 3: three rows meet both conditions. The amount column is not involved in this count.

Understanding the Syntax and Range Pairs

The first argument is a range to check; the second is the condition to apply to that range. Add further range-and-condition pairs in the same order. The square brackets in the syntax notation mean those later pairs are optional; do not type the brackets into a formula. A formula with three conditions has this pattern:

=COUNTIFS(range1, criterion1, range2, criterion2, range3, criterion3)

In Google Sheets, each criterion range must have the same number of rows and columns as the other criterion ranges. They do not have to start at the same cell, but using aligned ranges makes the row-by-row logic easier to verify. For example, if the region range is B2:B7, pair it with a status range covering the same records, such as C2:C7, not C3:C8. Misaligned ranges can produce an error or cause the formula to compare unintended records.

Keep the pairs in range-then-criterion order. A criterion by itself is not a valid argument in the right position, because Sheets needs to know which cells it should evaluate. You can use a literal criterion such as "Paid", a comparison expression, or a cell reference that contains the criterion.

Text Criteria, Wildcards, and Cell References

For an exact text match, put the text in straight double quotation marks. With representatives in A2:A7, this counts entries whose name is Dana:

=COUNTIFS(A2:A7,"Dana")

Text matching in COUNTIFS is not case-sensitive, so capitalization differences alone do not distinguish otherwise identical text. To match a pattern rather than a whole text value, use wildcards: an asterisk (*) matches any sequence of characters, while a question mark (?) matches one character. With names in A2:A7, this counts names beginning with D:

=COUNTIFS(A2:A7,"D*")

A wildcard can be useful for email addresses, product labels, or codes with a shared prefix. For instance, "*west*" matches text containing the word fragment west anywhere in the cell. If you need to match a literal asterisk or question mark instead of using it as a wildcard, precede that character with a tilde, such as "~*".

You can also keep the criterion in a cell so a user can change it without editing the formula. With regions in B2:B7 and the requested region in F1, use =COUNTIFS(B2:B7,F1). The value in F1 is treated as the criterion. This is especially convenient for summary sheets with dropdowns or input cells.

Comparison Operators and Numeric Conditions

Criteria can test values using operators such as greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). When the operator and the comparison value are typed together as a criterion, enclose the expression in quotation marks.

With regions in B2:B7 and amounts in D2:D7, this counts South-region records with an amount below 100:

=COUNTIFS(B2:B7,"South",D2:D7,"<100")

Based on the sample values 75 and 90 for the two South rows, the result is 2. If the threshold is stored in F1 instead, join the operator to the cell reference with an ampersand. With the same data layout and 100 in F1, use =COUNTIFS(B2:B7,"South",D2:D7,"<"&F1). The result remains 2. This construction is important: writing "<F1" would look for a comparison to literal text rather than use the value in F1.

Counting Dates Within a Period

Dates are stored as values in spreadsheets, even though they are displayed in familiar date formats. For dependable date counts, use actual date values in the cells and criteria, rather than dates entered as ambiguous text. A common pattern combines a lower bound and an upper bound on the same date range.

Suppose order dates are in A2:A10 and the start date of the period is in F1 while the end date is in F2. To count dates on or after the start and on or before the end, use:

=COUNTIFS(A2:A10,">="&F1,A2:A10,"<="&F2)

The two criteria apply to the same date range, so a date is counted only if it falls within both limits, including the boundary dates. If you need a month and already know its first and last dates, put those dates in the two cells. Another option is to build date criteria with DATE, for example ">="&DATE(2026,1,1). Using a function to create the date avoids relying on how a typed date string is interpreted.

For a timestamp column that includes times, an end-date criterion of less than or equal to midnight on the final day can leave out records later that day. In that situation, use an exclusive upper bound: dates/times before the first day of the next period. The exact boundary depends on the period you are counting, but the principle is to include every time on the final day without guessing its last possible time.

Blank Cells, Not-Equal Conditions, and OR Logic

To count blank cells in a range, use an empty-string criterion. With notes in E2:E20, =COUNTIFS(E2:E20,"") counts cells that Sheets treats as blank for this criterion. To count nonblank cells, use "<>" as the criterion. Check the source data if the result seems surprising: spaces, formulas returning empty text, and genuinely empty cells may not behave as a person visually expects in every data-cleaning situation.

To exclude a value, use the not-equal operator. With statuses in C2:C7, =COUNTIFS(C2:C7,"<>Pending") counts cells whose value is not Pending; it may also include blank cells because a blank is not the text Pending. If blanks should be excluded too, add a nonblank condition on the same range: =COUNTIFS(C2:C7,"<>Pending",C2:C7,"<>").

A frequent misconception is that adding another pair creates OR logic. It does not: separate pairs require all conditions to be true. For an either/or count, add separate COUNTIFS results. With regions in B2:B7 and statuses in C2:C7, this counts Paid rows in either North or South:

=COUNTIFS(B2:B7,"North",C2:C7,"Paid")+COUNTIFS(B2:B7,"South",C2:C7,"Paid")

The sample table returns 4: three North/Paid rows plus one South/Paid row. If the alternatives can overlap, adding counts may count the same record more than once. Choose mutually exclusive conditions or account for the overlap when constructing the formula.

Common Errors and Ways to Check a Formula

When a result is zero or an error appears, check the structure before changing the data. A quick review often reveals a mismatched range, an operator written outside quotation marks, or a criterion that differs from the cell content because of an extra space.

Count the arguments: each criterion range needs a following criterion, so the arguments must form complete pairs.
Confirm that all criterion ranges cover corresponding records and have matching dimensions.
Check that text criteria use straight quotation marks and that comparison operators are joined correctly to cell references.
Verify dates are real date values and numeric tests are applied to numeric values, not numbers stored as text.
Inspect imported text for leading or trailing spaces. A visually similar label may not be an exact match.

Google Sheets uses commas between function arguments in many spreadsheet locales, as in the examples above. Some locales use semicolons instead. If Sheets reports a formula parse error despite otherwise correct syntax, check the spreadsheet’s locale settings and use the separator expected there. The function name remains COUNTIFS.

For debugging, temporarily simplify the formula to one condition, confirm that result, and add the next pair. You can also put a criterion in a visible cell to see exactly what value is being passed. This makes it easier to distinguish a logic problem from a data-quality problem.

COUNTIFS Compared with Related Functions

Choose COUNTIF when you need to test only one range against one condition; it is the simpler one-condition counterpart. Choose COUNTIFS when multiple conditions must all be satisfied. If the goal is to add the values in matching rows rather than count them, use SUMIFS with a sum range and its criteria pairs. For example, a count of Paid orders and the total amount of Paid orders are different questions, even if both use a status condition.

For more complicated OR logic, wildcard patterns, or conditions that are awkward to express as criteria pairs, a formula built from functions such as FILTER and ROWS may be appropriate, but it is not automatically clearer. For ordinary multi-condition counting, COUNTIFS is usually the most direct tool. The best choice depends on whether you want a count, a sum, or the actual matching records.

Frequently Asked Questions

What is the correct COUNTIFS syntax in Google Sheets?

Use =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]). Pair every range with its criterion, and include additional pairs only when you need additional conditions.

Does COUNTIFS use AND or OR?

It uses AND across its range-and-criterion pairs: every condition must match for a row to count. For a basic OR condition, add separate COUNTIFS formulas, taking care not to double-count overlapping results.

Can COUNTIFS count dates in a month?

Yes. Apply a lower-bound and upper-bound criterion to the date range, using the month’s first date and the first date of the next month as an exclusive upper limit when timestamps may be present.

Why does COUNTIFS return zero when matching text looks correct?

Check for hidden spaces, different punctuation, a criterion with the wrong spelling, or a range that does not cover the intended rows. Also remember that text matching is not case-sensitive, so capitalization alone is generally not the cause.

Can I use a cell reference as a COUNTIFS criterion?

Yes. A criterion can refer to a cell directly, such as =COUNTIFS(B2:B7,F1). For a comparison operator and a cell value, join them with &, as in ">"&F1.

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.