Google Sheets COUNTIF With Multiple Ranges: How It Works

Google Sheets’ COUNTIF function accepts one range at a time, so to count matching values across separate ranges, add together one COUNTIF for each range. For example, with entries in A2:A100 and C2:C100, count the word Open using =COUNTIF(A2:A100,"Open")+COUNTIF(C2:C100,"Open").

Why COUNTIF Uses One Range

COUNTIF has two arguments: a range to inspect and a criterion to match. It counts cells in that range that meet the criterion. The function does not accept a list of unrelated ranges as its first argument, so a formula such as =COUNTIF(A2:A10,C2:C10,"Open") is not the way to combine them: it supplies three arguments, not the required range-and-criterion pair.

This distinction matters when a sheet has separate blocks of data—for example, current and archived records, two departments’ columns, or monthly lists. The ranges can be counted separately and their totals added. The approach is easy to inspect and works when the ranges have different sizes or are on different sheets.

Add COUNTIF Results for Separate Ranges

Suppose A2:A6 contains Open, Closed, Open, a blank, and Pending; C2:C6 contains Closed, Open, Pending, Open, and Closed. Each range has two cells matching Open, so their combined count is four.

With those entries in A2:A6 and C2:C6, this formula counts Open in both ranges and returns 4:

=COUNTIF(A2:A6,"Open")+COUNTIF(C2:C6,"Open")

The plus sign adds the two counts; it does not join or rearrange the original data. For three ranges, add a third COUNTIF, and so on. This is often the clearest choice because each range and its contribution are visible in the formula.

You can make the criterion editable by putting it in a cell. With the same sample data and Open in E2, the following formula also returns 4:

=COUNTIF(A2:A6,E2)+COUNTIF(C2:C6,E2)

Now changing E2 to Pending recalculates the total for Pending across both ranges. A cell reference is especially useful in a summary table, where a user can change the requested status without editing the formula.

Use an Array Literal to Stack Ranges

For same-shaped, single-column ranges, Google Sheets can also combine the values into a temporary vertical array inside the formula. A semicolon separates the rows of an array literal. The source cells remain where they are; the formula simply evaluates them together for this calculation.

With the sample entries still in A2:A6 and C2:C6, this formula returns 4:

=COUNTIF({A2:A6;C2:C6},"Open")

This can be compact when the ranges share a column count and are easy to stack. However, the plus-separated version is often simpler for readers to maintain, particularly when ranges come from different tabs or have different shapes. Array-literal separators can also depend on spreadsheet locale settings, so if Sheets reports a formula parse error, check the locale’s formula separator conventions or use separate COUNTIF calls.

Count Across More Than Two Ranges or Tabs

To include another range, continue adding individual counts. With values in A2:A100, C2:C100, and E2:E100, and the criterion Open in G2, the formula below adds the three results:

=COUNTIF(A2:A100,G2)+COUNTIF(C2:C100,G2)+COUNTIF(E2:E100,G2)

When ranges are on separate tabs, qualify each range with its sheet name. For a tab whose name contains a space, enclose the name in single quotation marks. For example, with data in January!A2:A100 and a tab named February Records containing A2:A100, and Open in E2, use:

=COUNTIF(January!A2:A100,$E$2)+COUNTIF('February Records'!A2:A100,$E$2)

The dollar signs keep E2 fixed if you copy the formula elsewhere. Use the sheet tab names as they actually appear; a misspelled name or missing quotation mark around a name with spaces can cause a reference error. The ranges do not have to be the same size when you add separate counts.

Choose the Right Criterion

The criterion may be text, a number, a comparison, a wildcard pattern, or a reference to a cell. Text matching in COUNTIF is not case-sensitive, so Open and OPEN match the same text. A criterion is applied independently to every range in a sum, which helps keep the counting rule consistent.

Exact text: Put the text in quotation marks in the formula, or refer to a cell containing it.
Numbers: A numeric criterion can be entered directly, such as =COUNTIF(A2:A20,5). If you use this example, it counts the number 5 in A2:A20.
Comparisons: Criteria such as greater than or not equal to are written as text, for example ">10" or "<>Closed".
Wildcards: An asterisk matches any sequence of characters, and a question mark matches one character. Use a tilde to treat a wildcard character literally.

For example, to count cells containing Open anywhere within the text, use a wildcard criterion. With values in A2:A20 and C2:C20, this returns the combined number of cells containing that text, including entries such as Open – urgent:

=COUNTIF(A2:A20,"*Open*")+COUNTIF(C2:C20,"*Open*")

Use wildcards deliberately: "Open*" matches text that begins with Open, while "*Open*" can match it in the middle. These patterns can also match longer words or phrases that contain those letters, so they are not a substitute for exact matching.

COUNTIF Versus COUNTIFS

COUNTIFS is useful when the goal is to test several conditions on paired ranges—for example, count rows where the status is Open and the region is West. It is not a special version of COUNTIF for adding unrelated blocks. Each criteria range in COUNTIFS must correspond in size and shape to the others, so the function can evaluate conditions row by row.

Suppose B2:B6 contains status values and C2:C6 contains the corresponding regions. If exactly three rows have Open in column B and West in column C, this formula returns 3:

=COUNTIFS(B2:B6,"Open",C2:C6,"West")

That result answers a different question from adding counts across separate ranges. To count Open in both A2:A6 and C2:C6, use two COUNTIF calls; to count rows meeting multiple conditions in corresponding columns, use COUNTIFS. For an OR rule, such as Open or Pending in the same range, add separate counts for those criteria. Take care if a cell could match more than one condition: adding categories can double-count it.

Common Mistakes and Troubleshooting

Passing ranges as extra arguments: Use one range and one criterion per COUNTIF, then add the results. Do not put several range references into one function call as if it accepted a range list.
Counting the same cells twice: If two ranges overlap, their shared cells are counted once for each range. Use non-overlapping ranges when each source cell should contribute only once.
Mixing exact and partial matching: "Open" matches the criterion as a value; "*Open*" also finds text that contains Open. Choose the pattern based on the data.
Numbers stored as text: A cell that looks like a number may be stored as text. Check formatting and imported data if numeric counts seem wrong; consistent data types make criteria more reliable.
Including headers: A header normally will not match a numeric or status criterion, but using data-only ranges avoids accidental matches and makes the formula easier to understand.
Locale and punctuation: Formula argument separators and array-literal separators can vary by locale. If the formula will not parse, check the spreadsheet’s locale under File > Settings, or use the plus-separated method.

Blank cells deserve special attention. A criterion referring to an empty cell or an empty-string result may count blanks, depending on the criterion used. If blanks are not part of the intended count, use an explicit nonblank criterion or make sure the criterion cell contains the expected value. Test a formula on a small, known sample before copying it into a large summary.

Make the Formula Easier to Maintain

For a small number of ranges, the direct sum is readable and dependable. Keep the ranges aligned with the actual data, and use absolute references for a criterion cell if formulas will be copied. Avoid selecting entire columns unnecessarily in a workbook with many such formulas; bounded ranges can make the intended data area clearer and reduce needless calculation work.

If the number of separate blocks keeps growing, consider whether they should be stored in one continuous data table with a column identifying month, department, or source. A single well-organized list is easier to filter, summarize, and count than many manually maintained ranges. In Google Sheets, a pivot table can summarize categories without writing a separate count formula for each one. That is a structural alternative, not a different syntax for making COUNTIF accept multiple ranges.

Frequently Asked Questions

Can COUNTIF use multiple ranges directly?
No. A COUNTIF call takes one range and one criterion. Add separate calls for separate ranges, or use a suitable array literal to stack compatible ranges.

Can I count the same word in two different columns?
Yes. Add one COUNTIF for each column or range, using the same criterion in each call. The sum is the total across both ranges.

Does COUNTIFS count across multiple separate ranges?
COUNTIFS evaluates multiple criteria across corresponding ranges, usually to find rows satisfying all conditions. To total matches from unrelated ranges, add separate COUNTIF results instead.

Can I use a cell as the criterion?
Yes. Use the cell reference as the second argument in each call, such as =COUNTIF(A2:A20,E2)+COUNTIF(C2:C20,E2). Changing E2 updates both counts.

Can the ranges be on different sheets?
Yes. Prefix each range with its tab name, and put single quotation marks around a sheet name that contains spaces. Add the resulting COUNTIF calls as usual.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.