Why does COUNTIF(MONTH(range), 3) return 0 or a wrong count?

COUNTIF's range argument evaluates only the first cell when given a function like `MONTH()` without ARRAYFORMULA. So `=COUNTIF(MONTH(C2:C3001), 3)` checks only whether `MONTH(C2)` equals 3 - the rest of the column is ignored. Wrap the range: `=COUNTIF(ARRAYFORMULA(MONTH(C2:C3001)), 3)` to force evaluation across all rows before COUNTIF runs.

Why is my January count too high when using ARRAYFORMULA(MONTH())?

`MONTH("")` returns 1 in Google Sheets. Every blank cell in your date column gets evaluated as January. If your range has 2,000 empty rows, January is inflated by 2,000. Fix it with COUNTIFS: `=COUNTIFS(ARRAYFORMULA(MONTH(C2:C3001)), 1, C2:C3001, "<>")`. The second criterion filters blanks before the month match runs, so only rows with actual dates are counted.

Can I get all 12 monthly counts in one formula without dragging?

Yes, using MAP with LAMBDA (available in Google Sheets since late 2023): `=MAP(ROW(INDIRECT("1:12")), LAMBDA(m, COUNTIFS(ARRAYFORMULA(MONTH(Transactions!$C$2:$C$3001)), m, Transactions!$C$2:$C$3001, "<>")))`. This spills 12 values vertically and includes blank-cell protection. TRANSPOSE it if your layout is horizontal. For pre-2023 files, drag the single-month COUNTIFS formula across 12 columns instead.

When should I use SUMPRODUCT instead of COUNTIF ARRAYFORMULA MONTH?

Switch to SUMPRODUCT when your dataset exceeds 50,000 rows, when you also need to multiply by a value column (summing instead of counting), or when you want to skip the blank-cell ARRAYFORMULA fix entirely. `=SUMPRODUCT((MONTH(C2:C3001)=3)*(C2:C3001<>""))` handles blanks natively and runs 2-3x faster than COUNTIF+ARRAYFORMULA on large ranges. For most FP&A models under 10,000 rows the performance difference is negligible.

Does this pattern work with YEAR() to filter by year-month?

Yes. Add a second ARRAYFORMULA criterion in COUNTIFS: `=COUNTIFS(ARRAYFORMULA(MONTH(C2:C3001)), 3, ARRAYFORMULA(YEAR(C2:C3001)), 2026, C2:C3001, "<>")`. This counts March 2026 transactions only. For a full year-month breakdown across a multi-year dataset - FY2024 through FY2026, for example - this is the cleanest approach that stays auditable without adding helper columns.

COUNTIF ARRAYFORMULA(MONTH()) in Google Sheets (2026)

The pattern shows up constantly in monthly close packs and contribution margin breakdowns: you have a flat transaction log, and you need counts sliced by calendar month without adding a helper column or exporting to pivot.

Why COUNTIF Rejects MONTH() Directly

COUNTIF's first argument expects a range reference - a concrete block of cells like C2:C3001. When you pass MONTH(C2:C3001) without ARRAYFORMULA, Google Sheets evaluates MONTH against only the first cell in the range. You get a single integer (the month of row 2), not an array, so the COUNTIF result is either 1 or 0 depending on whether that one date matches your criterion.

ARRAYFORMULA forces evaluation across the entire range before COUNTIF sees it. The result is a real in-memory array of integers (1 through 12), and COUNTIF matches against that normally.

Using COUNTIF with ARRAYFORMULA(MONTH()) to Generate 12 Monthly Counts

For a single month:

=COUNTIF(ARRAYFORMULA(MONTH(Transactions!C2:C3001)), 3)

For a board pack summary row where B4:M4 holds month numbers 1 through 12:

=COUNTIF(ARRAYFORMULA(MONTH(Transactions!$C$2:$C$3001)), B4)

Drag right through M4. Each cell picks up the month number from its header row and counts matching transaction rows. On 3,000 rows this recalculates across all 12 months in under a second.

If you want all 12 counts from a single formula with no drag, COUNTIF can't iterate a criteria array natively. Use MAP with LAMBDA (available in Google Sheets since late 2023):

=MAP(ROW(INDIRECT("1:12")), LAMBDA(m,
  COUNTIF(ARRAYFORMULA(MONTH(Transactions!$C$2:$C$3001)), m)
))

This spills 12 values vertically. TRANSPOSE it if your summary row runs horizontally. For pre-2023 fallback, drag the single-month formula - less elegant, but easier to audit when the CFO asks how you built it.

Fixing the Empty-Cell Bug in COUNTIF ARRAYFORMULA MONTH Formulas

This one silently corrupts January numbers in every model that uses open-ended ranges.

MONTH("") returns 1. Every blank cell in your date column gets counted as January. On a 3,000-row range where 2,000 rows are still empty (you're in Q1 of FY2026, data runs through March), January's count is inflated by 2,000.

The fix with COUNTIFS:

=COUNTIFS(
  ARRAYFORMULA(MONTH(Transactions!C2:C3001)), 3,
  Transactions!C2:C3001, "<>"
)

The second criterion drops blank dates before the month match runs. The same approach works when you drag across 12 months - just lock the range references and swap the hard-coded 3 for your month header cell.

You can also suppress blanks inside the ARRAYFORMULA itself:

=COUNTIF(
  ARRAYFORMULA(IF(Transactions!C2:C3001<>"", MONTH(Transactions!C2:C3001), "")),
  3
)

Both patterns work. The COUNTIFS version keeps the blank-exclusion logic explicit and auditable - important when someone is tracing a January variance.

SUMPRODUCT: When to Switch Away from COUNTIF ARRAYFORMULA

SUMPRODUCT handles the blank-cell problem and the month extraction in one pass, with no intermediate ARRAYFORMULA layer:

=SUMPRODUCT(
  (MONTH(Transactions!$C$2:$C$3001)=3) *
  (Transactions!$C$2:$C$3001<>"")
)

Performance is comparable for datasets under 10,000 rows. Above 50,000 rows, SUMPRODUCT tends to recalculate faster because it avoids materializing the intermediate array. According to Google's Sheets documentation, the spreadsheet cap is 10 million cells - on large transaction logs, SUMPRODUCT can run 2-3x faster than an equivalent COUNTIF+ARRAYFORMULA formula.

For summing rather than counting (revenue by month, not transaction count):

=SUMPRODUCT(
  (MONTH(Transactions!$C$2:$C$3001)=3) *
  (Transactions!$C$2:$C$3001<>"") *
  Transactions!$D$2:$D$3001
)

Where column D is transaction amount. This is the more common FP&A need anyway - the count is usually just a sanity check on the sum.

Approach	Blank-cell safe?	Multi-month spill?	Best for
`COUNTIF(ARRAYFORMULA(MONTH()))`	No (use COUNTIFS)	With MAP/LAMBDA	Transaction counts, <10k rows
`COUNTIFS(ARRAYFORMULA(MONTH()), ..., range, "<>")`	Yes	No (drag or MAP)	Auditable monthly counts
`SUMPRODUCT((MONTH()=m)*(range<>""))`	Yes	No (drag)	Counts or sums, any dataset size
`MAP(ROW(INDIRECT("1:12")), LAMBDA(m, COUNTIF(...)))`	Depends on inner formula	Yes	Board pack 12-month spill

Wiring It Into a Multi-Tab Model

In a quarterly board pack, the monthly counts feed a summary tab that other tabs reference. A clean setup:

Transactions tab (raw data): dates in column C, amounts in D, SKU or category in E.

Monthly Summary tab: month numbers 1-12 in B5:B16, then:

=COUNTIFS(
  ARRAYFORMULA(MONTH(Transactions!$C$2:$C$3001)), $B5,
  Transactions!$C$2:$C$3001, "<>",
  Transactions!$E$2:$E$3001, 'P&L'!$C$2
)

This counts transactions in month $B5 where the category matches whatever is in your P&L assumptions cell. The P&L tab then pulls:

=SUMIFS('Monthly Summary'!D:D, 'Monthly Summary'!B:B, ">=" & Assumptions!$B$3)

Two-tab references, all locked, no helper columns. Change a transaction date and the P&L moves. The audit trail runs from raw data through monthly summary to P&L without anything hidden in a pivot cache.

If you're wiring this into an existing model and want the formula written for your specific tab layout and column positions, ModelMonkey can read your sheet structure and write the COUNTIFS or SUMPRODUCT pattern with the correct references already filled in. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.