Count If Two Criteria Match: Excel & Sheets

This guide explains how to count cells that meet two conditions at once in Microsoft Excel and Google Sheets. You enter the data range and the two criteria, and you get back the number of matching cells using the COUNTIFS function. The steps and examples below work for both Excel 2010 and newer versions, as well as Google Sheets.

Formula

The COUNTIFS function counts the number of cells that meet multiple criteria. The syntax is:

=COUNTIFS(range1, criterion1, [range2, criterion2], ...)

Each range must have the same number of rows and columns. The criteria can be a number, text, a cell reference, or a logical expression (like “>10” or “<>Done”). Text criteria are case-insensitive in both Excel and Google Sheets.

Worked Example

Suppose column A lists product names (A2:A100) and column B lists quantities sold (B2:B100). You want to count how many times “Widget” appears in column A with a quantity greater than 10 in column B.

1. Set range1 to A2:A100 and criterion1 to "Widget".
2. Set range2 to B2:B100 and criterion2 to ">10".
3. The formula becomes: =COUNTIFS(A2:A100, "Widget", B2:B100, ">10").

If the data has 5 rows where column A is “Widget” and column B is greater than 10, the result is 5.

When to Use COUNTIFS vs. SUMPRODUCT

COUNTIFS is the simplest and fastest method for counting with multiple criteria. It works natively in both Excel and Google Sheets. However, there are cases where you might prefer SUMPRODUCT:

• When you need to count based on criteria that involve other functions (e.g., counting cells where the length is greater than 5).
• When you need to count with OR logic (e.g., count if column A is “Red” OR “Blue”).
• When you are using an older version of Excel (before 2007) that does not support COUNTIFS.

SUMPRODUCT is more flexible but slower on large datasets. For most everyday counting tasks, COUNTIFS is the right tool.

How to Use Cell References as Criteria

Instead of typing the criterion directly, you can reference a cell that contains the value. For example, if cell D1 contains “Widget”, the formula becomes:

=COUNTIFS(A2:A100, D1, B2:B100, ">10")

This makes it easy to change the criteria without editing the formula. For text criteria that use comparison operators (like “>” or “<>“), you must concatenate the operator with the cell reference: =COUNTIFS(A2:A100, ">"&D1, ...).

Common Mistakes and How to Avoid Them

• Mismatched range sizes: All ranges must have the same number of rows and columns. If they differ, the function returns #VALUE!.
• Using AND logic incorrectly: COUNTIFS inherently uses AND logic. If you need OR logic, you must use multiple COUNTIFS and add them together.
• Forgetting quotes for text: Text criteria must be enclosed in double quotes. For numbers, quotes are optional but recommended for consistency.
• Mixing data types: If a range contains numbers stored as text, the criteria may not match. Use the TEXT function or clean the data.

Counting with Wildcards

COUNTIFS supports wildcards: * (any sequence of characters), ? (any single character), and ~ (escape character). For example, to count all cells in A2:A100 that start with “W” and have a value greater than 10 in B2:B100:

=COUNTIFS(A2:A100, "W*", B2:B100, ">10")

Wildcards work with text criteria only, not with numeric comparisons.

Frequently Asked Questions

How do I count if two criteria match in Excel?

Use the COUNTIFS function: =COUNTIFS(range1, criterion1, range2, criterion2). For example, =COUNTIFS(A:A, "Yes", B:B, ">5") counts rows where column A is “Yes” and column B is greater than 5.

Can I use COUNTIFS with dates?

Yes. Dates must be entered as date values or as text in a recognized format. For example, =COUNTIFS(A:A, ">"&DATE(2024,1,1), B:B, "<"&DATE(2024,12,31)) counts dates in 2024.

How do I count with OR logic for two criteria?

COUNTIFS cannot handle OR directly. Instead, use multiple COUNTIFS and add them: =COUNTIFS(A:A, "Red", B:B, "Yes") + COUNTIFS(A:A, "Blue", B:B, "Yes").

Why does COUNTIFS return 0?

Check that the criteria are spelled correctly, ranges are the same size, and that there are no extra spaces. Also, ensure that text criteria are in quotes and comparison operators are used correctly.

Can I use COUNTIFS across different sheets?

Yes, you can reference ranges on other sheets, e.g., =COUNTIFS(Sheet2!A:A, "Yes", Sheet2!B:B, ">0").

Limitations and Performance

COUNTIFS is efficient for small to medium datasets (up to tens of thousands of rows). On very large datasets (hundreds of thousands of rows), it may slow down. In such cases, consider using a PivotTable or Power Query to summarize data. Also, note that COUNTIFS ignores cells that contain errors, which can be misleading if you expect to count them.

Related

• Razer Synapse Not Detecting Mouse? Fix It
• Clear All Jobs Print Queue: Full Guide
• Electrolux ZSP2320 Specs, Bags & Filters

in rng1 | Range 1 (e.g. A2:A100) | - | A2:A100 | 0
in crit1 | Criterion 1 (e.g. "Yes") | - | Yes | 0
in rng2 | Range 2 (e.g. B2:B100) | - | B2:B100 | 0
in crit2 | Criterion 2 (e.g. ">10") | - | >10 | 0
out result | Matching count | count | 0 | 0