COUNTIF With Non-Contiguous Ranges in Excel

Yes—you can use COUNTIF with non-contiguous ranges in Excel, but you cannot normally list separate ranges as the single range argument to one COUNTIF function. Add individual COUNTIF results, or use an array-based formula when you need one criterion applied to several ranges at once.

How COUNTIF handles ranges

COUNTIF has two arguments: COUNTIF(range, criteria). The range is the cells Excel checks, and the criteria specifies what counts as a match. A continuous range such as A2:A10 is one valid range. A selection such as A2:A10 and C2:C10 is non-contiguous: it contains separate areas divided by an unselected column.

COUNTIF’s range argument is designed to take one range reference. A comma-separated list of areas may be interpreted as multiple function arguments or produce an error, rather than making COUNTIF count all the areas. The dependable approach is to count each area separately and add the results.

Add separate COUNTIF results

Suppose you want to count the word “Complete” in A2:A10 and C2:C10, while ignoring column B. Use:

=COUNTIF(A2:A10,"Complete")+COUNTIF(C2:C10,"Complete")

Each COUNTIF checks one continuous range against the same criterion; the plus sign combines the counts. For a numeric criterion, such as values greater than 100, write:

=COUNTIF(A2:A10,">100")+COUNTIF(C2:C10,">100")

If the criterion is in cell E1, refer to that cell in both function calls:

=COUNTIF(A2:A10,E1)+COUNTIF(C2:C10,E1)

This is usually the clearest choice when there are only a few areas. It also makes the formula easy to inspect: each range and each test is explicit. Add another COUNTIF term for every additional area.

Use a cell reference for the criterion

A criterion can be text, a number, a comparison, a wildcard expression, or a reference to a cell. Using a reference is helpful when you want to change the test without editing the formula. For example, if E1 contains Complete, the formula above counts that value in both areas. If E1 contains a comparison operator and number, such as >100, COUNTIF reads it as a criterion.

When building a comparison from an operator and a cell value, join them with &. If E1 holds the threshold, use:

=COUNTIF(A2:A10,">"&E1)+COUNTIF(C2:C10,">"&E1)

This constructs a criterion such as >100. It is different from putting the text >E1 inside quotation marks, which would ask Excel to match that literal text.

Apply one criterion across several areas

For a longer list of ranges, an array formula can avoid repeating COUNTIF. In current Excel versions that support dynamic arrays, try:

=SUM(COUNTIF((A2:A10,C2:C10),E1))

The parenthesized range list creates a reference to multiple areas, COUNTIF returns a count for the areas, and SUM adds those counts. The precise behavior of array expressions can depend on the Excel version and how the formula is entered. If Excel rejects this construction, use the separate COUNTIF formula, which is broadly compatible, or test a supported alternative in your version.

Another option in many current Excel versions is to combine the selected cells into an array with VSTACK and count that array:

=COUNTIF(VSTACK(A2:A10,C2:C10),E1)

This stacks the two ranges vertically before applying the criterion. VSTACK is not available in every Excel version, so it is not the best choice for a workbook that must work in older releases. If you use it, check that the workbook’s intended users have a version with the function.

Choose a formula for the job

• A few ranges: Add COUNTIF calls. This is simple, transparent, and compatible with older Excel versions.
• Many ranges with a shared criterion: Consider an array approach such as VSTACK in a version that supports it, or maintain a formula that adds each separate count.
• Several criteria must all be true: COUNTIFS may be appropriate, but it expects corresponding criteria ranges of matching dimensions. It is not a direct replacement for counting one criterion across unrelated areas.
• Different criteria in different areas: Write a COUNTIF for each range and its own criterion, then add them only if the desired result is the combined total.

For example, to count “Complete” in column A and “Pending” in column C, use =COUNTIF(A2:A10,"Complete")+COUNTIF(C2:C10,"Pending"). That formula intentionally tests a different value in each area.

Common problems and limitations

• Using a union as if it were one ordinary range: A comma-separated range list inside COUNTIF is not the same as a continuous range. Split it into separate calls or use a compatible array method.
• Counting overlapping areas twice: If two specified ranges share cells, adding their COUNTIF results counts matching cells in the overlap twice. Make the ranges non-overlapping if each cell should count once.
• Incorrect quotation marks: Text criteria and literal comparisons need quotation marks, such as "Complete" or ">100". A cell reference used as the criterion is not quoted.
• Unexpected wildcard matches: COUNTIF recognizes * for any sequence of characters and ? for one character. To match one of these symbols literally, precede it with a tilde, such as ~*.
• Text that looks like a number: A value stored as text may not behave like a numeric value in every comparison. Check the source data and make sure the values use the intended type.

COUNTIF is not case-sensitive, so it does not distinguish, for example, between “complete” and “Complete.” If case-sensitive counting is required, a different formula approach is needed. Also, adding counts across ranges returns the total number of matching cells—not a count of distinct values or records unless the ranges and data are arranged to make those quantities equivalent.

Frequently Asked Questions

Can COUNTIF count two separate columns?
Yes. Add one COUNTIF for each column, such as =COUNTIF(A:A,"Yes")+COUNTIF(C:C,"Yes"). Use bounded ranges instead of whole columns when that better suits the workbook.

Can I use a non-contiguous range directly in COUNTIF?
Not as a simple list of separate areas in the ordinary range argument. Add separate COUNTIF results, or use an array formula supported by your Excel version.

How do I count cells across several ranges using a criterion in another cell?
Reference the criterion cell in each call, for example =COUNTIF(A2:A10,E1)+COUNTIF(C2:C10,E1).

Does COUNTIF count blank cells?
Use the criterion "" to count blank cells in each area, then add the results. Cells containing formulas that return an empty string can also be treated as blank by COUNTIF.

How do I count matches across non-adjacent ranges without double-counting?
Make sure the selected areas do not overlap. If they do, a matching cell in the overlap contributes to more than one COUNTIF result.

Bottom line

For ordinary non-contiguous ranges, the most reliable COUNTIF solution is to write one COUNTIF per area and add the results. Use a cell reference for a changeable criterion, avoid overlapping ranges when each cell should count once, and choose array-based formulas only when your Excel version supports them and the added complexity is useful.

Related

• Neural Networks: What Did They Just Hack?
• Copilot and OneDrive: What You Can Do
• LiteLLM: How a CI Scanner Led to Malicious PyPI Releases