COUNTIF with Two Variables: How to Count with Multiple Criteria

Formulas & functions · Updated November 22, 2026

Yes, you can count with two variables in Excel, and the standard way is to use the COUNTIFS function, which accepts multiple criteria ranges and conditions. For example, =COUNTIFS(A2:A20,"Apples",B2:B20,">10") counts rows where column A equals “Apples” and column B is greater than 10. If you need to count with OR logic (e.g., count if column A is “Apples” OR “Oranges”), you use a different approach, typically SUM(COUNTIF(...)) or SUMPRODUCT.

Why COUNTIF Alone Isn’t Enough

The single-criteria COUNTIF function counts cells that meet one condition. When you try to use it for two variables, Excel doesn’t allow a second criteria pair. For instance, =COUNTIF(A2:A20,"Apples",B2:B20,">10") returns a #VALUE! error because COUNTIF only accepts a range and a criterion. The dedicated function for multiple criteria is COUNTIFS, introduced in Excel 2007 and available in all later versions, including Microsoft 365, Excel 2021, and Excel for the web.

Using COUNTIFS for AND Logic

When you need both conditions to be true (AND logic), COUNTIFS is the right tool. Its syntax is:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Each range must have the same number of rows and columns. For example, with sales data in A2:A20 (product names) and B2:B20 (units sold), to count rows where product is “Apples” and units sold exceed 10:

  • Put the formula in cell D2: =COUNTIFS(A2:A20,"Apples",B2:B20,">10")
  • If the data has 3 such rows, the result is 3.

You can add more pairs up to 127 ranges in modern Excel. Criteria can be numbers, text, cell references, or expressions. For text, use quotes; for numbers, no quotes unless you use a comparison operator. For example, =COUNTIFS(A2:A20,"Apples",B2:B20,">"&E1) where E1 contains 10.

Handling OR Logic with Two Variables

If you want to count rows where either condition is true (OR logic), COUNTIFS won’t work because it requires all conditions to be met. Instead, use SUM with an array of COUNTIF results. For example, to count rows where column A is “Apples” OR “Oranges”:

=SUM(COUNTIF(A2:A20,{"Apples","Oranges"}))

This works because COUNTIF returns an array of two counts, and SUM adds them. In Excel 365, you can also use SUM(COUNTIFS(...)) with OR conditions across different columns, but it’s more complex.

Another method for OR logic across two different columns is to use SUMPRODUCT with addition:

=SUMPRODUCT(((A2:A20="Apples")+(B2:B20="Apples")>0)*1)

This counts rows where either column A or column B contains “Apples”. The double unary or *1 converts TRUE/FALSE to 1/0.

Counting with Multiple Criteria in the Same Column

Sometimes you need to count cells that meet two conditions in the same column, such as values between 10 and 20. COUNTIFS can handle this by using the same range twice with different criteria:

=COUNTIFS(B2:B20,">=10",B2:B20,"<=20")

This counts numbers between 10 and 20 inclusive. For text, you might count cells that start with "A" and end with "z", but that's trickier and often requires wildcards.

Common Mistakes and How to Avoid Them

  • Mismatched range sizes: All criteria ranges must have the same dimensions. If A2:A20 and B2:B21, you'll get a #VALUE! error.
  • Using COUNTIFS with OR logic: Remember COUNTIFS is AND-only. For OR, use SUM(COUNTIF(...)) or SUMPRODUCT.
  • Quoting numbers: If you use a comparison operator, quote the operator and concatenate the number: ">10" is fine, but ">10" without quotes is invalid.
  • Wildcard characters: In COUNTIFS, you can use * for any sequence and ? for a single character. For example, "A*" matches any text starting with A. To count literal asterisks, use a tilde ~ before it.
  • Case sensitivity: COUNTIFS is not case-sensitive. If you need case-sensitive counting, use SUMPRODUCT with EXACT.

Comparing COUNTIFS with SUMPRODUCT

SUMPRODUCT is an older, more flexible alternative that can handle both AND and OR logic without array formulas. For example, to count rows where A2:A20 is "Apples" and B2:B20 > 10, you could use:

=SUMPRODUCT((A2:A20="Apples")*(B2:B20>10))

This multiplies the TRUE/FALSE arrays, effectively ANDing them, and sums the results. For OR logic, replace * with + and ensure the result is >0. SUMPRODUCT is more verbose but works in all Excel versions. However, COUNTIFS is simpler and faster for straightforward AND criteria.

Using COUNTIFS in Google Sheets and Excel for the Web

COUNTIFS works identically in Google Sheets and Excel for the web. In Google Sheets, the syntax is the same: =COUNTIFS(A2:A20,"Apples",B2:B20,">10"). In Excel for the web, you can use COUNTIFS as well. For Mac Excel, the function and syntax are identical; only keyboard shortcuts differ, but no shortcut is needed for this function.

Frequently Asked Questions

Can I use COUNTIFS with dates?

Yes, dates are stored as numbers in Excel, so you can use comparison operators. For example, to count rows where date in C2:C20 is after January 1, 2024, use =COUNTIFS(C2:C20,">"&DATE(2024,1,1)) or reference a cell containing the date.

How do I count cells that match either of two values in the same column?

Use =SUM(COUNTIF(A2:A20,{"Red","Blue"})). This counts cells equal to "Red" plus cells equal to "Blue". If a cell contains both (impossible in a single cell), it would count once per condition, but that's not possible.

What if I need to count based on two variables but with OR logic across different columns?

Use SUMPRODUCT: =SUMPRODUCT(((A2:A20="X")+(B2:B20="Y")>0)*1). This counts rows where column A is X or column B is Y. The >0 ensures that if both are true, it's still counted once.

Why does my COUNTIFS return 0 when I know there are matches?

Check for leading/trailing spaces in your data. Use TRIM to clean data. Also, ensure the criteria range sizes match exactly. If you're using a cell reference for criteria, make sure the cell contains the exact text or number, and that you're not accidentally using a different data type (e.g., number vs. text).

Can I use COUNTIFS with wildcards for partial matches?

Yes, COUNTIFS supports wildcards. Use * for any sequence of characters, ? for any single character, and ~ to escape wildcards. For example, =COUNTIFS(A2:A20,"A*",B2:B20,"*B") counts rows where column A starts with A and column B ends with B.

Is there a limit to how many criteria pairs COUNTIFS can handle?

In modern Excel (2016 and later), COUNTIFS supports up to 127 range/criteria pairs. In older versions, it was 30. For most practical purposes, you'll never hit this limit.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.