Does COUNTIF return an array when the criteria argument is an array?

Yes. When you pass an array constant like `{"A","B","C"}` or a range reference as the criteria argument to COUNTIF, Excel evaluates the function once per element and returns an array of counts. In Excel 365 and Excel 2021, the result spills automatically. In Excel 2016 and earlier, you need to wrap the formula in SUM() to get a single result, or enter it as a CSE array formula (Ctrl+Shift+Enter) to get the full array.

Can I use a range reference as the criteria array instead of a hardcoded list?

Yes, and that's usually the better choice for production models. `=COUNTIF('Transactions'!$C:$C, Assumptions!$B$3:$B$12)` treats the 10-cell range B3:B12 as the criteria array, returning 10 counts. The output orientation matches the input: a vertical range produces a vertical spill, a horizontal range produces a horizontal spill. This approach makes the model easier to maintain because criteria changes happen in one place.

Why does SUM(COUNTIF(...)) work for OR-logic counts?

COUNTIF with array criteria returns an array of individual counts-one per criteria value. SUM() adds them together, collapsing the array to a single number. This works correctly when criteria values are mutually exclusive (no row can match two criteria simultaneously). If criteria can overlap-for example, if you're using wildcards-SUM(COUNTIF()) will double-count rows that match more than one criteria. Use SUMPRODUCT with Boolean multiplication to avoid that.

Can COUNTIFS also use array criteria?

Yes, but cleanly only when one criteria argument is an array and the rest are scalars. `=COUNTIFS(range1, {"A","B","C"}, range2, ">0")` returns 3 counts, one per element, each filtered by the range2 condition. Passing arrays to two different criteria arguments in the same COUNTIFS doesn't produce a 2D matrix-the behavior is element-wise and requires both arrays to match in size. For true cross-dimension counting, SUMPRODUCT is more predictable.

Does this behavior work the same in Google Sheets?

Yes. COUNTIF in Google Sheets also returns an array when given array criteria, and the result spills automatically (Google Sheets has always supported spilling). The syntax is identical: `=COUNTIF(range, {"A","B","C"})` returns 3 counts. One difference: Google Sheets' ARRAYFORMULA wrapper isn't needed here since the array expansion happens natively in COUNTIF's criteria argument. --- [Try ModelMonkey free for 14 days](/install) - it works in both Google Sheets and Excel.

Excel COUNTIF Array Criteria: Returns Array Results

This is one of those behaviors that isn't prominently documented but shows up constantly once you know it exists. Here's how it works, where it breaks, and when it's the right tool.

What Actually Happens When Criteria Is an Array

COUNTIF normally takes a single criteria value and returns a single count. Pass it an array constant instead and it evaluates each element independently, returning a matching array of results.

=COUNTIF('Pipeline'!$C:$C, {"Prospect","Qualified","Proposal","Closed Won","Closed Lost"})

This returns 5 numbers in a horizontal row: one count per stage. On a $18.4M ARR pipeline model where you're tracking 340 deals across 5 stages, that's your stage distribution table in a single formula.

In Excel 365, the result spills into 5 adjacent cells automatically. In Excel 2019/2021, it also spills. In Excel 2016 and earlier, the formula only returns the first value unless you either enter it as a CSE array formula (Ctrl+Shift+Enter) or wrap it in SUM().

Microsoft's COUNTIF documentation notes that the function "counts the number of cells that meet a criterion" but doesn't explicitly advertise the array expansion behavior. It's a consequence of how Excel handles array constants in function arguments-the function evaluates once per element.

Horizontal vs. Vertical: Which Way Does the Array Land?

The orientation of the output matches the orientation of the criteria array.

Comma-separated arrays {"a","b","c"} are horizontal. Results spill right across columns.

Semicolon-separated arrays {"a";"b";"c"} are vertical. Results spill down across rows.

For a board pack where stage counts feed into a summary table, vertical usually makes more sense-it aligns with a labeled list:

=COUNTIF('Deals'!$D:$D, {"Prospect";"Qualified";"Proposal";"Closed Won";"Closed Lost"})

Labels in B3:B7, this formula in C3, and your frequency table builds itself.

Using a Range Reference Instead of Hardcoded Arrays

Hardcoded array constants are fine for ad hoc analysis. For anything that goes to a bank syndicate or gets refreshed monthly, point the criteria at a named range or assumptions tab instead.

=COUNTIF('Transactions'!$C:$C, Assumptions!$B$3:$B$12)

Where B3:B12 contains your 10 revenue categories. When a category gets renamed or added, you update the Assumptions tab and every dependent formula adjusts. The criteria range here is vertical, so the output spills vertically-10 counts stacked in a column.

This is the version you actually want in a quarterly close model. Hardcoded strings in formulas are a maintenance problem waiting to happen.

Collapsing the Array: The SUM() Wrapper

Sometimes you need OR logic-count everything that matches any of several criteria. Wrap COUNTIF in SUM() and the array collapses to a single number.

=SUM(COUNTIF('Revenue'!$B:$B, {"Enterprise","Mid-Market","SMB"}))

This counts every row where the segment is Enterprise, Mid-Market, or SMB. Equivalent to three separate COUNTIFs added together, but in one cell.

Where this matters: contribution margin analysis by SKU cluster where you're collapsing 12 individual SKUs into 3 product families. One formula per family instead of 12 COUNTIFs summed manually.

Note that SUM(COUNTIF()) can double-count if the same row can match multiple criteria-which doesn't happen when criteria are mutually exclusive (stage names, segment codes, region labels) but does matter if you're using wildcards or overlapping ranges.

COUNTIFS and Array Criteria: One Array at a Time

COUNTIFS extends the same behavior, but with an important constraint: only one criteria argument should be an array at a time for the expansion to behave predictably.

=COUNTIFS(
  'Pipeline'!$C:$C, {"Prospect";"Qualified";"Proposal"},
  'Pipeline'!$F:$F, ">="&Assumptions!$B$1
)

This counts deals in each of 3 stages where the close date is at or after the quarter start in B1. The stage criteria is an array; the date criteria is a single scalar. Result: a 3-row vertical array of counts, each respecting the date filter.

The formula above is the kind of thing you'd build for a runway sensitivity on pipeline conversion-how many Qualified deals close before end of quarter at what stage mix.

When Multiple Array Criteria Break Things

If you try to pass arrays to two different criteria arguments in COUNTIFS, you don't get a 2D result matrix. You get an element-wise comparison that requires both arrays to be the same length and shape, and the behavior can be surprising enough that you shouldn't rely on it.

For true multi-dimension counting-count by stage AND by region simultaneously-the right tool is SUMPRODUCT:

=SUMPRODUCT(
  ('Pipeline'!$C$2:$C$500=D3) *
  ('Pipeline'!$G$2:$G$500=E3)
)

Or, if your model is on Excel 365 and the data is in a table, COUNTIFS with explicit range references per cell is usually cleaner than fighting COUNTIFS into array mode.

The array-in-criteria trick is genuinely useful for single-dimension frequency distributions. For cross-tab analysis, SUMPRODUCT or a pivot table wins.

Building a Frequency Table in Practice

A typical use case: contribution margin by SKU in a quarterly board pack. You have 3,200 transaction rows on the Transactions tab with a SKU column. The SKU list lives on Assumptions!$A$5:$A$22.

=COUNTIF('Transactions'!$B:$B, Assumptions!$A$5:$A$22)

18 counts in one formula, spilling into column B next to your labeled SKU list. Add a column for SUMIF on the same criteria to get revenue per SKU, and you have a workable contribution table without any helper columns or intermediate aggregation.

As of July 2026, this spill behavior is consistent across Excel 365, Excel 2021, and Google Sheets. Excel 2019 supports it. Excel 2016 requires CSE entry or the SUM() wrapper.

If you're building this kind of analysis regularly and want the criteria list, aggregation columns, and refresh logic managed through natural language rather than formula construction, ModelMonkey handles the cross-tab generation and can write the result directly back to the sheet-useful when the SKU list changes quarterly and you're rebuilding the structure more than you're maintaining it.