COUNTIFS with Multiple Criteria in Excel and Google Sheets
Purpose
COUNTIFS is the upgrade from COUNTIF that lets you count rows matching two or more conditions simultaneously. In my daily…
COUNTIFS is the upgrade from COUNTIF that lets you count rows matching two or more conditions simultaneously. In my daily work analysing sales data, IT ticket logs, and HR records, COUNTIFS comes up constantly. How many Sales department employees earned over a certain threshold? How many support tickets were high priority and still open? How many orders came from the East region in a specific date range? All of these are multi-condition count questions that COUNTIFS answers with a single formula. It uses AND logic — every condition must be true for a row to count — and it is available identically in Excel and Google Sheets, making it one of the most portable formulas in your toolkit.
Syntax
✓ Excel 2007+
✓ Google Sheets
= Same syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments
| Argument | Required | Description |
|---|---|---|
| criteria_range1 | Required | The first range of cells to test against the first condition. This can be a column of departments, dates, numbers, or text values. |
| criteria1 | Required | The condition to apply to criteria_range1. Can be a value like Sales, a number like 100, a comparison like >100, a wildcard like Sales*, or a cell reference. |
| criteria_range2 | Optional | A second range to test against a second condition. Must be the same number of rows and columns as criteria_range1 so corresponding rows are compared together. |
| criteria2 | Optional | The condition for criteria_range2. COUNTIFS counts a row only when it satisfies both criteria1 AND criteria2 simultaneously. Up to 127 range-criteria pairs are supported. |
How it works
COUNTIFS evaluates each row across all your criteria ranges simultaneously. A row is counted only when it satisfies every condition at the same time — this is AND logic. If you have five conditions, a row must pass all five to be counted. If it passes four but fails one it does not count. This is the fundamental difference from adding multiple COUNTIF formulas together, which uses OR logic and counts rows matching any condition. For OR logic with COUNTIFS you write separate COUNTIF or COUNTIFS formulas and add them, being careful to handle rows that match multiple conditions to avoid double-counting. Criteria can use comparison operators: > for greater than, < for less than, >= and <= for inclusive comparisons, and <> for not equal. Text criteria should be in quotes. To use a cell reference as criteria combine it with an operator: ">"&A1 means greater than the value in A1. Wildcards work in text criteria: asterisk matches any sequence of characters, question mark matches a single character. You can apply two criteria to the same column to define a range — for example two date conditions on the same date column to count rows within a date range.
Examples
1
Count employees in the Sales department who earned more than 50000.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Department | Salary | Count (Sales>50k) |
| 2 | Alice | Sales | 62000 | =COUNTIFS(B2:B11,"Sales",C2:C11,">50000") |
| 3 | Bob | Marketing | 48000 | |
| 4 | Carol | Sales | 55000 | 3 |
| 5 | David | Sales | 45000 | |
| 6 | Eve | Engineering | 71000 | |
| 7 | Frank | Marketing | 53000 | |
| 8 | Grace | Sales | 58000 | |
| 9 | Hank | HR | 42000 | |
| 10 | Iris | Engineering | 67000 | |
| 11 | James | Sales | 39000 |
Row 2: 3
— Alice (62000), Carol (55000) and Frank (58000) all work in Sales with salary above 50000.
David is in Sales but earns only 45000 which is below the 50000 threshold so he does not count. Eve earns well above 50000 but is in Engineering not Sales so she does not count. Grace earns above 50000 but is in Marketing. Both conditions — department equals Sales AND salary greater than 50000 — must be true simultaneously for a row to count.
2
Count orders that fall within Q1 2024 by using two date conditions on the same column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Customer | Date | Q1 Count |
| 2 | ORD-001 | Acme | 2024-01-15 | =COUNTIFS(C2:C11,">="&DATE(2024,1,1),C2:C11,"<"&DATE(2024,4,1)) |
| 3 | ORD-002 | Beta | 2024-02-08 | |
| 4 | ORD-003 | Gamma | 2023-12-20 | 5 |
| 5 | ORD-004 | Delta | 2024-03-30 | |
| 6 | ORD-005 | Epsilon | 2024-04-05 | |
| 7 | ORD-006 | Zeta | 2024-01-28 | |
| 8 | ORD-007 | Eta | 2024-02-14 | |
| 9 | ORD-008 | Theta | 2023-11-30 | |
| 10 | ORD-009 | Iota | 2024-03-12 | |
| 11 | ORD-010 | Kappa | 2024-05-01 |
Row 2: 5
— Orders 001, 002, 004, 006, 007, and 009 fall in Q1 2024. Orders 003 and 008 are in 2023. Orders 005 and 010 are in Q2.
You can apply two criteria to the same range. Using >= on one and < on another is the standard way to count within a date range.
3
Count open tickets from any region that starts with North using a wildcard.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ticket | Region | Status | Count |
| 2 | T-001 | North East | Open | =COUNTIFS(B2:B11,"North*",C2:C11,"Open") |
| 3 | T-002 | South West | Closed | |
| 4 | T-003 | North West | Open | 3 |
| 5 | T-004 | East | Open | |
| 6 | T-005 | North | Open | |
| 7 | T-006 | South | Closed | |
| 8 | T-007 | North East | Closed | |
| 9 | T-008 | West | Open | |
| 10 | T-009 | North West | Closed | |
| 11 | T-010 | East | Closed |
Row 2: 3
— T-001 (North East Open), T-003 (North West Open), and T-005 (North Open) all match both criteria.
The asterisk * matches any characters after North. North East, North West, and North all qualify. Wildcards work with text criteria only, not numbers or dates.
Common use cases
1. Count customer orders from a specific region with order value above a threshold
=COUNTIFS(A:A,"North",B:B,">5000")
2. Count IT support tickets that are high priority and currently open
=COUNTIFS(C:C,"High",D:D,"Open")
3. Count employees hired after a specific date who are still active
=COUNTIFS(E:E,">"&DATE(2022,1,1),F:F,"Active")
4. Count products in a specific category with stock levels below the reorder point
=COUNTIFS(G:G,"Electronics",H:H,"<10")
5. Count transactions between two dates in a specific account
=COUNTIFS(I:I,">="&DATE(2024,1,1),I:I,"<="&DATE(2024,12,31),J:J,"Checking")
Common errors
#VALUE!
The criteria ranges are different sizes. All ranges in COUNTIFS must cover exactly the same number of rows and columns for the row-by-row comparison to work correctly.
Fix: Check that every criteria range covers the same rows. If one range is B2:B100 all other ranges must also be 99 rows. Using full column references like B:B for all ranges avoids this mismatch.
Returns 0 unexpectedly
Extra spaces in cell values are the most common cause. COUNTIFS(A:A,"Sales") finds no matches if cells contain " Sales" or "Sales " with leading or trailing spaces.
Fix: Use TRIM to clean your data column before comparing. Also check data types — if the salary column contains numbers stored as text, numeric comparisons like >50000 will find no matches.
Double-counting with OR logic
Adding two COUNTIFS formulas for OR logic counts rows matching both conditions twice.
Fix: Subtract the rows matching both conditions: =COUNTIFS(A:A,"North")+COUNTIFS(A:A,"South")-COUNTIFS(A:A,"North",A:A,"South") — though note the last term handles a range not text values, use SUMPRODUCT for complex OR scenarios.
Tips and variations
Use cell references for dynamic criteria
Instead of hardcoding criteria values in the formula, reference cells containing the criteria. This lets you change the filter values without editing the formula and makes dashboards and summary tables interactive. Put the department name in cell F1 and the salary threshold in G1, then write COUNTIFS referencing those cells.
=COUNTIFS(B:B,F1,C:C,">"&G1)
Count between two dates using two criteria on the same column
To count rows where a date falls within a range, apply two criteria to the same date column. One condition for the start date using >= and another for the end date using <=. This is one of the most common COUNTIFS patterns in business reporting and works for any numeric range not just dates.
=COUNTIFS(D:D,">="&DATE(2024,1,1),D:D,"<="&DATE(2024,12,31))
Use wildcards for partial text matching
COUNTIFS supports wildcard characters in text criteria. An asterisk matches any sequence of characters, so "Sales*" matches Sales, Salesperson, Sales Manager, and anything else starting with Sales. A question mark matches exactly one character. This is useful for counting by category prefixes or partial codes.
=COUNTIFS(A:A,"Sales*",B:B,">50000")
Excel vs Google Sheets
Excel vs Google Sheets
COUNTIFS works identically in Microsoft Excel and Google Sheets. Same syntax, same argument order, same wildcard support, same date handling, same comparison operators. The 127 criteria pair limit is the same in both applications. Formulas copy between Excel and Google Sheets without modification. This is one of the most reliably portable counting formulas available across both platforms.
Related reading
Frequently asked questions
COUNTIF handles a single condition — it counts cells in one range that meet one criterion. COUNTIFS handles multiple conditions simultaneously — it counts rows where all specified conditions are true at the same time. Use COUNTIF for simple single-condition counts. Use COUNTIFS whenever you need to count rows satisfying two or more conditions together. In modern practice COUNTIFS is preferred even for single conditions because it is more flexible when you later need to add criteria.
COUNTIFS always uses AND logic — every condition must be true simultaneously. For OR logic write separate COUNTIFS formulas for each condition and add them together. If rows might match multiple conditions and you do not want to double count, subtract the overlap: =COUNTIFS(A:A,"North",B:B,"Open")+COUNTIFS(A:A,"South",B:B,"Open") counts open tickets in North OR South without overlap since one row cannot be in both regions simultaneously.
The three most common causes are: extra spaces in cell data where a cell contains Sales with a trailing space but your criteria is Sales without one — use TRIM; data type mismatch where salary values are stored as text so numeric comparisons find nothing — check by selecting a cell and looking at its alignment; and range size mismatch where criteria ranges cover different numbers of rows causing the #VALUE! error or unexpected zeros.
Yes, COUNTIFS is completely identical in both applications. Same syntax, same argument order, same wildcard support, same comparison operator support, same date handling. Formulas copy between Excel and Google Sheets without any changes needed. This makes COUNTIFS one of the most reliably portable counting formulas for teams working across both platforms.
Yes, COUNTIFS handles dates the same as any other value. To count rows with a specific date use the DATE function in the criteria: =COUNTIFS(A:A,DATE(2024,6,15)). To count rows within a date range apply two criteria to the same date column using >= and <=. You can also reference cells containing dates directly as criteria. Make sure your date column contains actual date values and not text that looks like dates — check by verifying dates are right-aligned in cells.