Function Problems

How to Count a Cell Value Across Multiple Non-Adjacent Ranges in Excel

Aamir Naveed Akram Oct 1, 2026 868 views

Question details

The user wants to count the total occurrences of a specific drop-down value (such as "CANC") across multiple nonadjacent columns and rows, and display the result in a single cell.

How to Count a Cell Value Across Multiple Ranges in Excel

Product: Excel
Device & OS: not provided
Scenario: Consolidating the count of a specific text string or drop-down selection from multiple independent, non-contiguous ranges into one summary cell.
Observed behavior: The user needs a working formula to count criteria across multiple disjointed ranges, which standard COUNTIF cannot do natively in a single argument.

Before you start

Identify all the specific cell ranges you need to evaluate, and ensure the criteria text (e.g., "CANC") is spelled exactly as it appears in the source data.

Solution 1Recommended

Use SUM, COUNTIF, and INDIRECT for Multiple Ranges

This method is highly efficient for Excel 365 users, allowing you to evaluate multiple disjointed ranges by passing an array of ranges into the INDIRECT function.

By combining SUM, COUNTIF, and INDIRECT, you can process an array of non-adjacent ranges in one clean formula rather than writing multiple separate COUNTIF statements.

Select the Result Cell

Click on the single cell where you want the final total count to appear (for example, E41).

Enter the Array Formula

Type the formula: =SUM(COUNTIF(INDIRECT({"L7:L38","Q7:Q38","V7:V38","AA7:AA38","AF7:AF38","AK7:AK38","AP7:AP38","AU7:AU38","AZ7:AZ38","BE7:BE38"}),"CANC"))

Calculate the Result

Press Enter. Excel will evaluate each range listed in the array, count the occurrences of "CANC", and sum them up to display the total.

Use SUM, COUNTIF, and INDIRECT for Multiple Ranges

Syntax Tip: Ensure you enclose the text string you are searching for in double quotes (e.g., "CANC") and wrap the range addresses within curly brackets {} and double quotes.

Solution 2

Combine Multiple COUNTIF Functions

If you are using an older version of Excel that does not fully support array processing with INDIRECT, you can add individual COUNTIF functions together.

WPS Spreadsheet

Count Values Across Ranges Effortlessly in WPS Spreadsheet

WPS Office provides a highly compatible and user-friendly spreadsheet tool that fully supports advanced formulas like SUM, COUNTIF, and INDIRECT for counting values across multiple non-adjacent ranges seamlessly.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing the drop-down values you want to count.
2. Select the Summary Cell: Click on the cell where you want to display the consolidated count.
3. Apply the Formula: Input the =SUM(COUNTIF(...)) formula referencing your multiple non-adjacent ranges and press Enter to instantly calculate your total.

Fully compatible with Microsoft Excel formulas, functions, and formats.Free, lightweight, and fast alternative to heavy office suites.Seamless handling of array formulas to quickly analyze complex datasets.Intuitive interface perfect for both beginners and advanced data analysts.

microsoft office alternative - wps office

Frequently Asked Questions

Can I count numerical values instead of text using this formula?

Yes. To count numbers, simply replace the text criteria (e.g., "CANC") in the formula with the specific number you want to count. You do not need to enclose numeric values in double quotes.

Why does my INDIRECT formula return a #REF! error?

The #REF! error usually occurs if the text strings defining the ranges inside the INDIRECT array are typed incorrectly, or if they reference a closed external workbook. Double-check that your range addresses are exact and correctly enclosed in quotes.

How do I count cells that contain partial text?

You can use wildcard characters in your COUNTIF criteria. For example, using "*CANC*" will count any cell that contains the word "CANC" anywhere within its text string.

Is there a limit to how many ranges I can include in the INDIRECT array?

While there isn't a strict limit to the number of ranges in the array, overly long arrays can make the formula difficult to read and manage. If you have dozens of ranges, consider reorganizing your data or adding individual COUNTIF functions.

Excel & Data Problems