COUNTIF with Multiple Ranges: How to Do It in Excel

Formulas & functions · Updated November 22, 2026

You cannot use a single COUNTIF function with multiple separate ranges in Excel—COUNTIF only accepts one range. To count across multiple ranges, use COUNTIFS with paired criteria, or use SUMPRODUCT with COUNTIF when the ranges are not adjacent. This article explains the exact formulas, when to use each, and how to avoid common errors.

Why COUNTIF Only Accepts One Range

COUNTIF is designed to evaluate a single contiguous range against one condition. Its syntax is COUNTIF(range, criteria). The range argument must be a single reference like A2:A10; you cannot write COUNTIF(A2:A10, C2:C10, "Yes"). Excel will return a #VALUE! error because the function expects exactly two arguments. This limitation exists because COUNTIF was introduced in early Excel versions, and the more flexible COUNTIFS was added later to handle multiple conditions and ranges.

Using COUNTIFS for Multiple Ranges and Multiple Criteria

When you need to count cells across multiple ranges, COUNTIFS is the direct replacement. Its syntax is COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). Each range must have the same number of rows and columns, and each range is paired with its own criterion. For example, to count how many times “Apples” appears in range A2:A10 and “Red” appears in range B2:B10, use:

=COUNTIFS(A2:A10, "Apples", B2:B10, "Red")

This counts rows where both conditions are true. Note that COUNTIFS does not require the ranges to be adjacent; they can be on different sheets, such as Sheet1!A2:A10 and Sheet2!B2:B10, as long as the dimensions match.

Counting the Same Criterion Across Multiple Ranges

If you want to count occurrences of a single value across several separate ranges (e.g., count all “Yes” in columns A, C, and E), COUNTIFS cannot do it directly because it applies an AND logic—it would require the same cell to be “Yes” in all ranges. Instead, use SUMPRODUCT with COUNTIF:

=SUMPRODUCT(COUNTIF(A2:A10, "Yes") + COUNTIF(C2:C10, "Yes") + COUNTIF(E2:E10, "Yes"))

This works because COUNTIF returns an array of counts (one per range), and SUMPRODUCT sums them. For a more compact version that handles many ranges, you can use:

=SUMPRODUCT(COUNTIF(INDIRECT({"A2:A10","C2:C10","E2:E10"}), "Yes"))

The INDIRECT version requires the ranges as text and works in Excel 365, 2021, and older versions. However, INDIRECT is volatile and can slow down large workbooks, so the first formula is safer for most cases.

Counting with OR Logic Across Multiple Ranges

Sometimes you need to count cells that meet any of several criteria across multiple ranges. For example, count rows where column A is “Apples” OR column B is “Pears”. Use SUMPRODUCT with an OR condition:

=SUMPRODUCT((A2:A10="Apples") + (B2:B10="Pears"))

This adds the boolean arrays (TRUE=1, FALSE=0) and sums them. If a row meets both conditions, it is counted twice; to count each row only once, use:

=SUMPRODUCT(--((A2:A10="Apples") + (B2:B10="Pears") > 0))

The double unary (–) converts TRUE/FALSE to 1/0, and the >0 ensures only rows with at least one match are counted.

Common Errors and How to Fix Them

  • #VALUE! error: Occurs when you pass multiple ranges to COUNTIF. Solution: switch to COUNTIFS or SUMPRODUCT.
  • Mismatched range sizes: In COUNTIFS, all ranges must have the same number of rows and columns. If not, Excel returns #VALUE!. Check that each range covers the same cells.
  • Counting blank cells: To count blanks across multiple ranges, use COUNTIF with “” or COUNTIFS with “”. For example, =COUNTIFS(A2:A10,"",B2:B10,"") counts rows where both are blank.
  • Case sensitivity: COUNTIF and COUNTIFS are not case-sensitive. If you need case-sensitive counting, use SUMPRODUCT with EXACT, e.g., =SUMPRODUCT(--EXACT(A2:A10,"Yes")).

Comparing COUNTIF, COUNTIFS, and SUMPRODUCT

COUNTIF is the simplest and fastest for a single range and condition. COUNTIFS is the best choice for multiple conditions across multiple ranges with AND logic. SUMPRODUCT is the most flexible, allowing OR logic, arrays, and even calculations within the criteria. Performance-wise, COUNTIFS is optimized and generally faster than SUMPRODUCT on large datasets. For most users, COUNTIFS should be the first tool, and SUMPRODUCT reserved for cases where COUNTIFS cannot express the logic.

Alternatives: PivotTables and Dynamic Arrays

If you are working with large datasets and need to count across multiple ranges frequently, consider a PivotTable. It can summarize counts across multiple columns by restructuring the data. In Excel 365, you can also use the FILTER function to extract matching rows and then count them with ROWS, but this is less direct. For example, to count rows where column A is “X” or column B is “Y”:

=ROWS(FILTER(A2:B10, (A2:A10="X") + (B2:B10="Y")))

This returns the count of rows that meet the OR condition, counting each row once. Note that FILTER requires Excel 2021 or Microsoft 365.

Frequently Asked Questions

Can I use COUNTIF with two separate columns?

Yes, but not with a single COUNTIF. Use COUNTIFS if you want to count rows where both columns meet criteria (AND), or use SUMPRODUCT if you want to count occurrences in either column (OR). For example, to count how many times “Yes” appears in either column A or column B, use =SUMPRODUCT((A2:A10="Yes") + (B2:B10="Yes")).

How do I count across multiple sheets with COUNTIF?

COUNTIF cannot reference multiple sheets in one range. You can either use multiple COUNTIF functions added together, like =COUNTIF(Sheet1!A2:A10,"Yes") + COUNTIF(Sheet2!A2:A10,"Yes"), or use SUMPRODUCT with a 3D reference if the sheets are consecutive, but 3D references are not supported in COUNTIF. The simplest is to add separate COUNTIF results.

Does COUNTIFS work with non-adjacent ranges?

Yes, COUNTIFS can use ranges on different sheets or non-adjacent columns, as long as each range has the same dimensions. For example, =COUNTIFS(A2:A10,"X",C2:C10,"Y") works even if columns B is in between.

Why does my formula return #VALUE! when I use multiple ranges?

This usually happens when you try to use COUNTIF with multiple ranges, which is invalid. Switch to COUNTIFS, or if you need OR logic, use SUMPRODUCT. Also check that all ranges in COUNTIFS have the same number of rows and columns.

Can I count cells that contain text from a list across multiple ranges?

Yes, you can use SUMPRODUCT with COUNTIF and an array of criteria. For example, to count cells in A2:A10 that contain any of the values in D2:D4, use =SUMPRODUCT(COUNTIF(A2:A10, D2:D4)). To apply this across multiple ranges, add more COUNTIF terms inside the SUMPRODUCT.

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.