Does COUNTIF with array criteria work differently in Google Sheets vs Excel?

Yes. In Google Sheets, `=COUNTIF(range, array_criteria)` spills automatically - the result is a dynamic array. In Excel, the same formula requires Ctrl+Shift+Enter in older versions or relies on dynamic array support (Excel 365 / Excel 2021+). As of June 2026, Google Sheets handles this more reliably across all plan types because its grid is natively array-aware, whereas Excel's behavior depends on whether the file uses the dynamic array engine.

Why does COUNTIF with array criteria return 0 or a single value instead of an array?

Two common causes. First, if you're referencing a named range or structured table reference that resolves to a single cell due to implicit intersection, the array behavior collapses. Second, if you accidentally included the formula cell itself in the spill area, Google Sheets suppresses the output to avoid a circular reference. Check that the cells below (or to the right) the formula are empty before it fires.

Can I use COUNTIF array results directly in a chart or will I need to expand them first?

Charts in Google Sheets can reference a range that contains spilled results, but you reference the entire spill range explicitly (e.g., B4:B9), not just the formula cell. If the spill range shifts because your criteria list grows, your chart range won't update automatically - you'd need a named range or OFFSET-based reference to handle that cleanly.

How do I combine COUNTIF array output with SUMIF output in the same formula?

Use SUMPRODUCT with both arrays as arguments. For example, to get average deal value per stage: ``` =IFERROR( SUMIF('Pipeline'!$C:$C, Dashboard!$A$4:$A$9, 'Pipeline'!$E:$E) / COUNTIF('Pipeline'!$C:$C, Dashboard!$A$4:$A$9), 0 ) ``` Both SUMIF and COUNTIF return arrays here, and dividing them element-wise gives average deal size per stage. IFERROR handles stages with zero count to avoid #DIV/0!.

Does passing a multi-column range as the criterion work?

No, not in the way you might expect. COUNTIF treats a multi-column criteria range by flattening or erroring depending on the version. For multi-column criteria lookups, you're better off using a helper column that concatenates the relevant fields, then pass that concatenated column as your criterion range. Alternatively, COUNTIFS with ARRAYFORMULA handles multi-condition counting more cleanly than trying to force COUNTIF to work with a 2D criteria range.

COUNTIF Array Criterion in Google Sheets: Returns Array

What "Returns Array" Actually Means

COUNTIF in Google Sheets is array-native when the criterion argument is a range or a hardcoded array literal. Pass a single value and you get a single count. Pass a range of N values and you get a column (or row) of N counts, oriented to match the shape of your criteria range.

=COUNTIF('CRM Export'!D:D, Dashboard!$B$3:$B$8)

If B3:B8 is a vertical range with six deal stages, this spills a 6x1 column of counts starting at the formula cell. Google Sheets handles the spill automatically - no Ctrl+Shift+Enter, no ARRAYFORMULA wrapper.

With a hardcoded array literal it works the same way:

=COUNTIF('CRM Export'!D:D, {"Prospecting","Qualified","Proposal","Negotiation","Won","Lost"})

That returns a 1x6 row instead, because the literal {} syntax uses commas for horizontal, semicolons for vertical. In practice you want a referenced range, not a literal - it makes the dashboard updatable when someone renames a stage.

COUNTIF vs COUNTIFS with Array Criteria (the Gotcha)

This is where models break. COUNTIF natively handles array criteria. COUNTIFS does not.

If you write:

=COUNTIFS('Transactions'!B:B, Assumptions!$B$4:$B$9, 'Transactions'!C:C, "Closed")

...expecting an array of counts filtered by both department AND status, you'll get 0 or a single value depending on context. COUNTIFS evaluates the criteria intersectionally (AND logic), and when you pass an array it doesn't loop over it the way COUNTIF does.

The fix is ARRAYFORMULA:

=ARRAYFORMULA(COUNTIFS('Transactions'!B:B, Assumptions!$B$4:$B$9, 'Transactions'!C:C, "Closed"))

Now it returns one count per row in $B$4:$B$9. This distinction bites people constantly. COUNTIF: native array return. COUNTIFS: needs ARRAYFORMULA to loop.

Four Patterns That Actually Show Up in Models

1. Pipeline Stage Distribution

You want a deal count at each stage, pulling from a CRM export tab:

=COUNTIF('CRM Export'!$D:$D, Dashboard!$A$4:$A$9)

Column D on the export has stage names. A4:A9 on Dashboard has your six stages. This spills into B4:B9. Wrap with =IFERROR() if the export tab sometimes doesn't exist.

2. Headcount by Department (for OpEx Build)

Your HC tab has department codes in column C. Assumptions tab has the department list in B4:B11. You want counts feeding into your opex model by department:

=TRANSPOSE(COUNTIF('Headcount'!$C:$C, Assumptions!$B$4:$B$11))

TRANSPOSE flips a vertical range to horizontal so it lines up with column headers in your P&L. Without it, the spill goes down instead of across.

3. Aging Buckets for AP or AR

You have invoice dates in column B and want counts by aging bucket. Build a helper on your Assumptions tab with bucket labels (0-30, 31-60, 61-90, 90+), then use COUNTIFS with ARRAYFORMULA:

=ARRAYFORMULA(COUNTIFS(
  'AP Detail'!$B:$B, ">=" & TODAY() - Assumptions!$F$3:$F$6,
  'AP Detail'!$B:$B, "<" & TODAY() - Assumptions!$F$2:$F$5,
  'AP Detail'!$E:$E, "Open"
))

Where F2:F5 and F3:F6 are the upper/lower day bounds for each bucket. This gives you four counts in one formula.

4. Frequency Count for Sensitivity Analysis

You're running a hiring sensitivity model and want to know how many scenarios land in each headcount band. Your scenario output tab has 500 rows of modeled end-states:

=COUNTIF('Scenarios'!$D:$D, ">=" & Sensitivity!$C$2:$C$8) - COUNTIF('Scenarios'!$D:$D, ">=" & Sensitivity!$D$2:$D$8)

Where C2:C8 is the lower bound of each band and D2:D8 is the upper bound. Subtracting the two COUNTIF arrays gives you counts within each range. This works cleanly because both COUNTIFs return arrays and array subtraction is element-wise.

Controlling Output Orientation

When the criteria range is vertical (a column), the output is vertical. When it's horizontal (a row), the output is horizontal. If you need to flip orientation, TRANSPOSE is the right tool. If you need to collapse the array to a single number (say, total non-zero counts), use SUMPRODUCT:

=SUMPRODUCT((COUNTIF('P&L'!$B:$B, 'Cost Centers'!$A$2:$A$15) > 0) * 1)

That counts how many cost centers from your master list actually appear in the P&L - useful for reconciliation checks before a board pack goes out.

The One Catch Worth Knowing

If your criteria contain duplicates, COUNTIF counts them independently. =COUNTIF(A:A, {"East","East","West"}) returns {5,5,3} - the "East" count appears twice. When you're pulling criteria from a tab that might have dupes (like a raw CRM export), clean the criteria range first or use UNIQUE:

=COUNTIF('Transactions'!$C:$C, UNIQUE('Transactions'!$C:$C))

This self-referencing pattern auto-generates the count for every distinct value in the column, which is useful for exploratory analysis but too dynamic for a controlled model layout.

Where ModelMonkey Fits

Building the criteria list is often the tedious part. When a new cost center appears in the GL, someone has to update the Assumptions tab before the array formula picks it up. ModelMonkey can monitor your source tabs and flag when values appear in source data that aren't in your criteria lists - or just rewrite the formula to pull from UNIQUE automatically if you want a dynamic version. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.