Why doesn't COUNTIF work inside ARRAYFORMULA the way I expect?

COUNTIF already returns an array when given an array as its criteria argument - you often don't need `ARRAYFORMULA` at all. The wrapper becomes necessary when you're combining COUNTIF with non-array-native functions, like using `IF()` or date arithmetic inside the criteria. If `ARRAYFORMULA(COUNTIF(...))` is returning a single value instead of a column, check whether your criteria argument is actually an array reference or a single cell.

Can I use COUNTIF(ARRAYFORMULA(...)) to count values from a calculated column?

Yes, and it's the right pattern when you want to avoid helper columns. `=COUNTIF(ARRAYFORMULA(C2:C5000/B2:B5000), ">"&0.385)` counts rows where the calculated margin exceeds 38.5% without materializing a margin column. Comparison operators work reliably; wildcard matching (`*`, `?`) does not work against in-memory arrays and will return incorrect results. Use SUMPRODUCT if you need wildcard or text-pattern conditions against a calculated range.

How do I flag duplicates with a single ARRAYFORMULA instead of dragging COUNTIF down?

Use `COUNTIFS` with a `ROW()` trick to build an incremental range per row: `=ARRAYFORMULA(IF(COUNTIFS(A$2:A, A2:A, ROW(A$2:A), " 1, "DUPLICATE", ""))`. The anchored `A$2:A` gives you the full column, while `ROW(A$2:A) <= ROW(A2:A)` constrains the count to rows at or above the current position - replicating the expanding-range behavior of the dragged formula in a single cell.

What's the performance difference between ARRAYFORMULA(COUNTIFS) and SUMPRODUCT for large datasets?

On a 50,000-row dataset, both run in the 2-5 second range for recalculation. `COUNTIF(ARRAYFORMULA(...))` is faster for single-count scenarios (under 1 second) because it scans an in-memory array rather than evaluating criteria against a sheet range. The biggest performance gain across all three patterns is bounding ranges to actual data (`D2:D5000` instead of `D:D`), which reduces recalculation time by 60-70% by preventing full-column scans.

How do I use ARRAYFORMULA(COUNTIFS) to count across multiple tabs?

Reference the external tab directly in the COUNTIFS range arguments: `=ARRAYFORMULA(COUNTIFS('P&L'!B:B, 'P&L'!B2:B, 'P&L'!C:C, ">="&Assumptions!$B$3, 'P&L'!D:D, Summary!A2:A20))`. The criteria array (`Summary!A2:A20`) drives how many rows the formula returns - 19 counts in this case. Keep the count range and criteria range arguments as full-column references and the criteria array as a bounded range, or you'll get dimension mismatch errors when the sheets have different row counts.

COUNTIF + ARRAYFORMULA in Google Sheets (2026)

Q: What's the performance difference between ARRAYFORMULA(COUNTIFS) and SUMPRODUCT for large datasets?

On a 50,000-row dataset, both run in the 2-5 second range for recalculation. `COUNTIF(ARRAYFORMULA(...))` is faster for single-count scenarios (under 1 second) because it scans an in-memory array rather than evaluating criteria against a sheet range. The biggest performance gain across all three patterns is bounding ranges to actual data (`D2:D5000` instead of `D:D`), which reduces recalculation time by 60-70% by preventing full-column scans.

Q: How do I use ARRAYFORMULA(COUNTIFS) to count across multiple tabs?

Reference the external tab directly in the COUNTIFS range arguments: `=ARRAYFORMULA(COUNTIFS('P&L'!B:B, 'P&L'!B2:B, 'P&L'!C:C, ">="&Assumptions!$B$3, 'P&L'!D:D, Summary!A2:A20))`. The criteria array (`Summary!A2:A20`) drives how many rows the formula returns - 19 counts in this case. Keep the count range and criteria range arguments as full-column references and the criteria array as a bounded range, or you'll get dimension mismatch errors when the sheets have different row counts.

The Two Patterns, Briefly

They look similar enough that analysts often grab the wrong one. The difference is the operation order: one produces an array of counts, the other counts values inside a virtual column.

Pattern	What it returns	When you'd use it
`ARRAYFORMULA(COUNTIFS(...))`	One count per row in the criteria range	Count occurrences of each entity across a lookup range
`COUNTIF(ARRAYFORMULA(...))`	A single count of values in a calculated column	Count how many rows meet a calculated condition without a helper column

Neither is wrong. They solve different problems.

ARRAYFORMULA(COUNTIFS): One Count Per Row

This is the pattern most analysts are actually looking for. You want a column of counts that expands automatically as rows are added - no dragging, no breaking when the CFO asks you to insert a product line at row 14.

=ARRAYFORMULA(COUNTIFS(
  'Transactions'!B:B,  'SKU Summary'!A2:A50,    -- match each SKU
  'Transactions'!C:C,  ">="&Assumptions!$B$3,   -- on/after period start
  'Transactions'!D:D,  "<="&Assumptions!$B$4    -- on/before period end
))

One formula in 'SKU Summary'!B2 returns 49 counts down the column. Add a SKU to A51 and the count appears automatically.

One thing that trips people up: COUNTIF already returns an array when given an array as the criteria argument, even without ARRAYFORMULA. =COUNTIF('Transactions'!B:B, A2:A50) spills to 49 rows on its own. You need the ARRAYFORMULA wrapper when you're combining COUNTIF with functions that don't natively expand - like IF, LEN, or date arithmetic in the criteria.

-- This needs ARRAYFORMULA because the criteria involves a calculation
=ARRAYFORMULA(COUNTIFS(
  'P&L'!B:B,  Assumptions!$B$2,
  'P&L'!C:C,  ">="&(Assumptions!$B$3 - 90),   -- prior 90 days, needs ARRAYFORMULA to expand
  'P&L'!D:D,  D2:D100
))

COUNTIF(ARRAYFORMULA): Counting a Calculated Column

Less common, genuinely useful when your count criteria depends on a calculation you don't want to materialize as a helper column.

Say you need to count how many of 3,200 revenue transactions in a deal model have a gross margin above 38.5%, but you're not creating a helper column mid-model:

=COUNTIF(
  ARRAYFORMULA('Transactions'!D2:D3200 / 'Transactions'!C2:C3200),
  ">"&Assumptions!$C$8
)

The inner ARRAYFORMULA evaluates D/C for every row into an in-memory array. COUNTIF scans that array and returns a single count. No helper column, no named range.

Same pattern works for contribution margin analysis across SKUs:

=COUNTIF(
  ARRAYFORMULA('SKU Detail'!E2:E800 - 'SKU Detail'!F2:F800 - 'SKU Detail'!G2:G800),
  ">"&Pricing!$B$15
)

The gotcha: COUNTIF against an in-memory array handles comparison operators (>, <, >=, <=) reliably. Wildcard pattern matching (*, ?) is unreliable against calculated arrays. According to Google's Sheets function documentation, COUNTIF wildcard support applies to text ranges - not derived numeric arrays. If you need wildcard matching against a calculated column, materialize it first or use SUMPRODUCT.

Duplicate Detection at Scale

This is where ARRAYFORMULA + COUNTIF earns real time savings in AP and deal tracking models.

The dragged-down version - =COUNTIF($A$2:A2, A2)>1 - breaks when rows are inserted and requires manual extension every time the source data grows. The self-contained ARRAYFORMULA version:

=ARRAYFORMULA(
  IF(
    COUNTIFS(
      'AP Detail'!A$2:A,  'AP Detail'!A2:A,
      ROW('AP Detail'!A$2:A),  "<="&ROW('AP Detail'!A2:A)
    ) > 1,
    "DUPLICATE",
    ""
  )
)

Place this once at the top of the flag column. As invoices are added, the duplicate detection expands automatically. The mechanism is the ROW() trick: ROW(A$2:A) is anchored at the top while ROW(A2:A) expands with each row, so the COUNTIFS evaluates an incrementally growing range for each position - exactly replicating the dragged-down pattern in a single formula.

On a 5,000-row AP export, this runs in under 2 seconds on a standard Sheets file (tested June 2026).

When SUMPRODUCT Is the Right Call

ARRAYFORMULA(COUNTIFS) runs into reliability issues with 4+ nested conditions, especially when the criteria range includes blank cells or mixed types. If your formula is returning unexpected zeros or #VALUE! errors at certain row counts, SUMPRODUCT is usually cleaner:

=SUMPRODUCT(
  ('P&L'!B2:B5000 = Assumptions!$B$2) *
  ('P&L'!C2:C5000 >= Assumptions!$B$3) *
  ('P&L'!D2:D5000 = D12) *
  ('P&L'!E2:E5000 <> "")
)

Each condition evaluates to a 1/0 array. Multiplying them applies AND logic. No ARRAYFORMULA required, no criteria range size limits, no wildcard ambiguity.

For an FCFF model where you're counting periods meeting multiple conditions - positive UFCF, post-investment horizon, specific entity - SUMPRODUCT over a named range is also easier to audit in a model review. Someone stepping through your DCF tabs can read ('Cash Flow'!UFCF >= 0) more immediately than parsing a multi-argument COUNTIFS.

Performance at Scale

Both patterns have real recalculation costs at enterprise row counts. From testing on a 50,000-row transaction file in Google Sheets (June 2026):

ARRAYFORMULA(COUNTIFS) with 3 criteria against 50,000 rows: 3-5 seconds on recalculate
COUNTIF(ARRAYFORMULA) on the same row count: under 1 second (builds the array once, then scans it sequentially)
SUMPRODUCT with 4 conditions on 50,000 rows: 2-4 seconds, comparable to ARRAYFORMULA(COUNTIFS)

The single biggest performance lever is bounding your ranges. D2:D5000 vs D:D reduces recalculation time by 60-70% in most cases, because Sheets stops evaluating at the specified boundary rather than scanning 10 million cells. On a quarterly board pack that recalculates every time the Assumptions tab changes, that difference is noticeable.

Anchoring ranges to the last row of your data with something like INDIRECT("D2:D"&COUNTA('Transactions'!A:A)+1) is an option, though it introduces volatility. A hardcoded row ceiling with 10-15% buffer is usually the better trade-off for models under active edit.

If you're working in a complex multi-tab model and spending time wiring these formulas together across sheets, ModelMonkey can generate the cross-tab COUNTIFS and ARRAYFORMULA structures for you directly in Sheets - describe what you're counting and it writes the scoped formula. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.