COUNTIFS counts rows that meet two or more conditions, such as orders from a particular region above a chosen value.
The key is matching each condition to a range of the same size, and a few examples make that pattern easy to adapt.
COUNTIFS syntax and how it works
In Excel and Google Sheets, COUNTIFS applies multiple conditions at once.
A row is counted only when it satisfies every condition, so the criteria work together as an AND test.
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...)Each criteria range is the group of cells to check, and its paired criterion describes what should match.
The first range and criterion are required; add more range-and-criterion pairs as needed.
For example, if column A contains regions and column B contains sales amounts, this formula counts rows where the region is East and the amount is greater than 100:
=COUNTIFS(A2:A100,"East",B2:B100,">100")Each pair can refer to a different column.
All criteria ranges must cover the same number of rows or columns.
Keeping their starting and ending cells aligned helps avoid errors and makes the formula easier to audit.
Combine criteria across columns
Suppose a worksheet has Region in A2:A100, Status in B2:B100, and Sales in C2:C100.
To count completed orders from the East with sales of at least 500, use:
=COUNTIFS(A2:A100,"East",B2:B100,"Complete",C2:C100,">=500")The result is the number of rows where all three conditions are true.
Replace the sample values and ranges with the columns in your own data.
If the data is stored in an Excel table named Orders, structured references can make the formula more readable:
=COUNTIFS(Orders[Region],"East",Orders[Status],"Complete",Orders[Sales],">=500")In Google Sheets, the same function and criteria-pair structure work with regular ranges.
For both spreadsheet apps, avoid including a header row unless you intend to count it as data.
Use criteria from cells
Cell references let you change a condition without editing the formula.
If E2 contains a region and F2 contains a sales threshold, use:
=COUNTIFS(A2:A100,E2,B2:B100,">"&F2)The ampersand joins the comparison operator with the value in F2.
If F2 contains 100, the second criterion becomes greater than 100.
To include values equal to the threshold, change the operator to >=.
For a criterion assembled from a cell, put the operator inside quotation marks and join it to the reference.
A formula such as ">"&F2 is different from writing ">F2", which treats F2 as literal text.
Count dates within a range
Dates can be compared with the same operators as numbers.
To count records in January 2026 when dates are in A2:A100, use a start date inclusive and an end date exclusive:
=COUNTIFS(A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))This includes January 1 and excludes February 1.
The exclusive end boundary also works when cells contain timestamps, because times later on January 31 remain below midnight on February 1.
If your spreadsheet uses another locale, function argument separators may be semicolons instead of commas.
Use wildcards for text matches
COUNTIFS supports wildcards in text criteria.
The asterisk (*) represents any number of characters, while the question mark (?) represents one character.
To count names in A2:A100 that begin with “Sam,” use:
=COUNTIFS(A2:A100,"Sam*")To count entries with exactly one character after “Sam,” use "Sam?".
Wildcards are useful for partial matches, but they can make criteria broader than expected.
To search for a literal asterisk or question mark, prefix it with a tilde, such as "~*".
Understand AND and OR conditions
Multiple criteria pairs in one COUNTIFS formula use AND logic.
For instance, a row must match both East and Complete in this formula:
=COUNTIFS(A2:A100,"East",B2:B100,"Complete")To count rows that match either East or West, add two COUNTIFS results:
=COUNTIFS(A2:A100,"East")+COUNTIFS(A2:A100,"West")This sum is suitable when each row can have only one region.
If the conditions can overlap, a row may be counted twice, so consider whether the categories are mutually exclusive before adding results.
Common errors and edge cases
- Unequal range sizes: Keep each criteria range aligned and the same length.
Mixing A2:A100 with B2:B99 can cause errors or unexpected results. - Numbers stored as text: A numeric-looking value stored as text may not behave like a number in comparisons.
Check the source data type if a threshold count looks wrong. - Spaces in text: “East” and “East ” may not match as expected.
Trim inconsistent spaces in the source data or use a helper column to clean it. - Blank criteria: A criterion that refers to an empty cell can be interpreted as a blank match.
Confirm that criteria cells contain the intended value. - Case sensitivity: COUNTIFS text matching is generally not case-sensitive.
Use a different approach if upper- and lowercase distinctions matter. - Locale differences: Excel and Sheets installations may use semicolons as argument separators, and date formats can vary by locale.
Compatibility in Excel and Google Sheets
COUNTIFS is available in modern versions of Microsoft Excel, including Excel 2007 and later, and in Google Sheets.
Its basic syntax and AND behavior are consistent across these applications.
Excel versions differ in newer features such as dynamic arrays, but ordinary COUNTIFS formulas do not depend on them.
For a reliable formula, verify that every range has matching dimensions, that dates are actual date values, and that comparison operators are joined to cell references correctly.
These checks make COUNTIFS with multiple criteria easier to adapt for sales reports, attendance logs, inventory lists, and other row-based data.