Google Sheets COUNTIF With Multiple Criteria Explained

Google Sheets’ COUNTIF function accepts one condition; to count cells that meet multiple conditions at the same time, use COUNTIFS. For alternatives that meet either condition, add separate COUNTIF results, taking care not to count the same row twice.

COUNTIF versus COUNTIFS

COUNTIF checks one range against one criterion. Its basic structure is =COUNTIF(range, criterion). For example, it can count every occurrence of a particular region, status, or number threshold in a column. It cannot take a second criterion as another argument; adding one to the function will produce an error.

COUNTIFS is the function designed for multiple conditions. Its structure is =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]). Each range is paired with its own criterion, and a row is counted only when all the paired tests are true. The conditions therefore work as AND logic by default. Google Sheets supports COUNTIFS in the browser, and Excel also has a COUNTIFS function with a similar purpose and syntax.

Choose based on the question you are asking:

Use COUNTIF to count matches against one condition.
Use COUNTIFS to require two or more conditions on the same row.
Use multiple COUNTIF calculations when a match to any one of several alternatives should count.

The distinction matters because “East and Paid” is not the same as “East or Paid.” Picking the wrong function can produce a plausible-looking number that answers a different question.

Count rows that meet multiple conditions

Suppose a small sales table has region in column A, payment status in column B, and sales amount in column C. Rows 2–7 contain these records: East/Paid/120, West/Paid/90, East/Pending/80, East/Paid/150, West/Pending/110, and East/Paid/70. To count transactions that are both in the East region and marked Paid, pair each condition with its column.

With regions in A2:A7 and statuses in B2:B7, this formula returns 3 because three rows satisfy both conditions:

=COUNTIFS(A2:A7,"East",B2:B7,"Paid")

With regions in A2:A7 and sales amounts in C2:C7, this formula returns 2 because two East transactions exceed 100:

=COUNTIFS(A2:A7,"East",C2:C7,">100")

Each criteria range is checked row by row: A2 is considered with B2 or C2, then A3 with B3 or C3, and so on. This is useful for questions such as how many orders are in a particular status and region, how many students passed and attended, or how many entries fall within a category and a score band. Conditions can refer to the same column more than once, too—for example, one test can set a lower numeric limit and another an upper limit.

Use operators and cell references in criteria

Text criteria can be written directly in quotation marks, such as "Paid". Comparison criteria for numbers use operators including >, >=, <, and <=. Put an operator and its fixed value together in a quoted criterion, as in ">100". For a changing threshold, join the operator to a cell reference with the ampersand.

With sales amounts in C2:C7 and a minimum threshold in E1, this formula counts sales at least as large as the value in E1:

=COUNTIFS(C2:C7,">="&E1)

With regions in A2:A7, statuses in B2:B7, and the requested region in E1 and status in F1, this formula uses those cells as criteria:

=COUNTIFS(A2:A7,E1,B2:B7,F1)

Cell references are useful for dashboards and reusable reports: change the selection in E1 or F1 and the count updates without editing the formula. If a criterion contains a comparison operator and a cell reference, keep the operator inside quotation marks and concatenate the reference outside them. A common mistake is writing ">=E1", which makes Sheets look for text that includes E1 rather than comparing values to the cell’s contents.

Count alternatives with OR logic

COUNTIFS applies AND logic across its criteria pairs. If the task is to count rows whose region is East or West, count each condition separately and add the results. With regions in A2:A7, this formula returns 6 for the sample table:

=COUNTIF(A2:A7,"East")+COUNTIF(A2:A7,"West")

That addition works because each row has only one of those region values. But adding counts can double-count a row if the alternatives can both be true. For example, suppose the question is how many records are either in the East region or Paid. With regions in A2:A7 and statuses in B2:B7, use inclusion-exclusion: count East, count Paid, then subtract rows that are both East and Paid.

=COUNTIFS(A2:A7,"East")+COUNTIFS(B2:B7,"Paid")-COUNTIFS(A2:A7,"East",B2:B7,"Paid")

In the sample table, four rows are East, four are Paid, and three are both, so the result is 4 + 4 − 3 = 5 distinct rows. This overlap check is the key difference between a simple OR across mutually exclusive categories and an OR across conditions that may overlap. If the alternatives are numerous or more complicated, consider making the logic explicit in helper columns so it is easier to review.

Dates, blanks, and wildcard text

Dates are numbers internally in Sheets, so date comparisons can be built with operators and date values. A reliable method is to use DATE(year, month, day) rather than typing a date as text, which can be interpreted differently depending on locale. With dates in D2:D100, this counts dates in calendar year 2025: the start is inclusive and the start of 2026 is excluded.

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

Using an exclusive upper boundary is helpful when date cells may include times as well as dates; a timestamp on December 31 still falls before January 1. To count nonblank cells, use the not-equal-to-empty-string criterion. With entries in A2:A100, the formula is =COUNTIF(A2:A100,"<>"). COUNTIF and COUNTIFS criteria also support wildcards: * matches any number of characters, and ? matches one character. With product names in A2:A100, =COUNTIF(A2:A100,"*tea*") counts entries containing “tea.” Matching is not case-sensitive. To match a literal asterisk or question mark rather than use it as a wildcard, precede it with a tilde, such as "~*".

Keep ranges aligned and diagnose errors

In COUNTIFS, every criteria range should cover the same number of rows and correspond to the same records. For example, pairing A2:A100 with B2:B99 can cause an array-size error or an unintended setup. Start and end all ranges on the same rows, and avoid including a header in only one of them. If a formula returns zero unexpectedly, check for extra spaces, inconsistent labels such as “Paid” and “Paid ”, or a criterion that points to the wrong cell.

Other frequent issues include:

Wrong logic: COUNTIFS requires every condition to be true. If you mean “this or that,” add separate counts or use a carefully designed alternative.
Numbers stored as text: A value that looks numeric may not behave like a number in comparisons. Check the source data and its formatting or conversion.
Quoted references: In a dynamic condition, use concatenation such as ">="&E1, not a quoted string containing the cell address.
Hidden whitespace: Imported data can contain leading or trailing spaces that make text criteria fail. Cleaning the source values can be better than complicating the counting formula.

When a result seems wrong, test one criterion at a time with COUNTIF, then add conditions gradually. This helps identify whether the issue is a particular range, criterion, or the underlying data rather than the overall formula structure.

When to use another approach

For a straightforward count, COUNTIFS is usually the clearest choice because its criteria are visible beside their ranges. Use a helper column when the condition needs several transformations or when coworkers will need to inspect the rule. For example, a helper column could label a row “Review” when a combination of fields meets a business rule; a simple count can then count those labels. That approach uses extra cells but can make complex logic easier to audit.

For OR conditions, separate COUNTIF calculations are easy to understand when the groups cannot overlap. If they can overlap, subtract the intersection as shown above or create a single row-level helper test to ensure each record contributes at most once. Avoid forcing a long formula into one cell merely to save space if it makes the counting rule difficult to explain. Document what counts as a match, especially when criteria include boundaries such as “at least 100” or date ranges; inclusive and exclusive limits change the result.

COUNTIF and COUNTIFS return counts, not filtered lists or sums. If you need to see the matching records, use a filter or a suitable filtering formula; if you need to add values from matching rows, use a conditional sum function such as SUMIFS instead. Keeping the task clear—count, list, or sum—helps select the right function.

Frequently Asked Questions

Can COUNTIF use two criteria in Google Sheets?
Not directly. COUNTIF takes one criterion. Use COUNTIFS for multiple conditions that must all be true, or combine separate COUNTIF calculations for alternatives.

Does COUNTIFS mean AND or OR?
COUNTIFS applies AND logic: a row is counted only when it meets every specified criteria pair. For OR logic, add separate counts, accounting for overlap so a row is not counted twice.

How do I count values greater than a cell’s value?
Join the comparison operator to the cell reference. For example, if amounts are in C2:C100 and the limit is in E1, use =COUNTIFS(C2:C100,">"&E1).

Can COUNTIFS check a date range?
Yes. Use one condition for the lower date limit and another for the upper limit on the same date range. DATE(year, month, day) can make the boundaries unambiguous.

Is COUNTIFS case-sensitive?
No. COUNTIF and COUNTIFS text matching is generally case-insensitive. If capitalization must distinguish values, use a different approach based on an exact, case-sensitive comparison rather than relying on these functions.

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.