Why does ARRAYFORMULA(COUNTIF(...)) return 0 for month ranges?

Dates in Google Sheets are stored as serial numbers, not as month integers. When you pass `3` as COUNTIF criteria against a date column, you're asking how many cells equal the number 3. None do - they're all 5-digit serial numbers like 45731 - so the result is 0. ARRAYFORMULA doesn't fix this because COUNTIF isn't designed to expand criteria evaluation row-by-row. Switch to COUNTIFS with DATE() bounds, which compares date values against date values correctly.

What's the difference between COUNTIFS and SUMPRODUCT for monthly counts?

COUNTIFS with date bounds is faster on large datasets and cleaner to read when your condition is a calendar month or date range. SUMPRODUCT is more flexible when you need conditions involving MONTH(), YEAR(), or other calculated values that don't fit neatly into range comparisons. On a 5,000-row dataset, both return results in under a second when using bounded ranges. For most board pack monthly summaries, COUNTIFS with DATE() and EOMONTH() is the better choice.

Can ARRAYFORMULA + COUNTIFS generate all 12 monthly counts at once?

Yes - COUNTIFS (unlike COUNTIF) works with ARRAYFORMULA when given array criteria. Use `SEQUENCE(12)` for the month numbers and wrap the whole thing in ARRAYFORMULA: `=ARRAYFORMULA(COUNTIFS(dates, ">="&DATE(year, SEQUENCE(12), 1), dates, "<"&DATE(year, SEQUENCE(12)+1, 1)))`. This spills 12 monthly counts from a single formula cell. The same structure works with SUMIFS for monthly revenue totals.

My COUNTIFS formula returns 0 even though data exists for that month - what's wrong?

Almost certainly your date column contains text strings that look like dates rather than actual date values. COUNTIFS date comparisons only work against real date serial numbers, not text. Check by looking at cell alignment: text-formatted dates align left, real dates align right. Fix with `=ARRAYFORMULA(DATEVALUE(A2:A1000))` or `=VALUE()` applied to the column, then use your COUNTIFS against the converted values. This is the most common failure point when importing from Salesforce, NetSuite, or any CSV export.

Does SUMPRODUCT slow down on large sheets?

Using full-column references like `A:A` inside SUMPRODUCT causes lag because it evaluates every blank cell too. Use bounded ranges sized slightly beyond your actual data - `$A$2:$A$5000` rather than `$A:$A`. A 3-criteria SUMPRODUCT across 5,000 rows with bounded references typically recalculates in under half a second. If you're running 10+ SUMPRODUCT formulas on the same sheet, COUNTIFS with date bounds is generally faster and the better choice for high-formula-count dashboards.

COUNTIF + ARRAYFORMULA by Month in Google Sheets (2026)

Q: My COUNTIFS formula returns 0 even though data exists for that month - what's wrong?

Almost certainly your date column contains text strings that look like dates rather than actual date values. COUNTIFS date comparisons only work against real date serial numbers, not text. Check by looking at cell alignment: text-formatted dates align left, real dates align right. Fix with `=ARRAYFORMULA(DATEVALUE(A2:A1000))` or `=VALUE()` applied to the column, then use your COUNTIFS against the converted values. This is the most common failure point when importing from Salesforce, NetSuite, or any CSV export.

Q: Does SUMPRODUCT slow down on large sheets?

Using full-column references like `A:A` inside SUMPRODUCT causes lag because it evaluates every blank cell too. Use bounded ranges sized slightly beyond your actual data - `$A$2:$A$5000` rather than `$A:$A`. A 3-criteria SUMPRODUCT across 5,000 rows with bounded references typically recalculates in under half a second. If you're running 10+ SUMPRODUCT formulas on the same sheet, COUNTIFS with date bounds is generally faster and the better choice for high-formula-count dashboards.

Why COUNTIF Breaks on Month Ranges

Dates in Google Sheets are stored as serial numbers. January 1, 2025 is 45658. March 15, 2025 is 45731. When you write =COUNTIF('Deals'!A:A, 3) hoping to count March closings, you're asking how many cells equal the integer 3. None of them do - they're all 5-digit serial numbers - so the result is 0.

The MONTH() workaround doesn't fix it. If you try:

=COUNTIF('Deals'!A:A, MONTH(TODAY())=3)

Google Sheets evaluates MONTH(TODAY())=3 first, gets TRUE or FALSE, then counts how many cells in column A equal that single boolean. That's not a month filter - it's a logical test masquerading as one.

Wrapping it in ARRAYFORMULA doesn't help either. According to Google's Sheets documentation, COUNTIF's criteria argument accepts "a string, number, regular expression or reference to a cell containing such." It's not designed to expand an array of criteria row-by-row. ARRAYFORMULA can't force that behavior onto a function that isn't built for it.

The 3 Patterns That Actually Work

Pattern	Best For	Needs ARRAYFORMULA?	Spills?
COUNTIFS + DATE/EOMONTH	Single-month count per cell	No	No
SUMPRODUCT	Flexible multi-condition counts	No	No
ARRAYFORMULA + COUNTIFS + SEQUENCE	Full-year summary, one formula	Yes	Yes (12 rows)

Pattern 1: COUNTIFS with Date Bounds

This is the most readable approach for a monthly summary table. Construct an explicit date range - first of month to last of month - and COUNTIFS handles the rest.

If your Summary tab has month numbers in column A (1 through 12) and a year in Assumptions!$B$1:

=COUNTIFS(
  'Deals'!$A:$A, ">="&DATE(Assumptions!$B$1, Summary!A2, 1),
  'Deals'!$A:$A, "<="&EOMONTH(DATE(Assumptions!$B$1, Summary!A2, 1), 0)
)

EOMONTH handles February's 28/29-day variance and every other month-end edge case. No manual day counts needed.

For a pipeline model tracking deals by segment, layer in more criteria. COUNTIFS supports up to 127 criteria pairs per Google's documentation, so you have room:

=COUNTIFS(
  'Deals'!$A:$A, ">="&DATE(Assumptions!$B$1, Summary!A2, 1),
  'Deals'!$A:$A, "<="&EOMONTH(DATE(Assumptions!$B$1, Summary!A2, 1), 0),
  'Deals'!$E:$E, "Enterprise"
)

Pattern 2: SUMPRODUCT

SUMPRODUCT is natively array-aware. It evaluates arrays element-by-element without any ARRAYFORMULA wrapper. This makes it the right tool when you want to match on MONTH() and YEAR() directly rather than constructing date bounds:

=SUMPRODUCT(
  (MONTH('Deals'!$A$2:$A$1000)=Summary!A2) *
  (YEAR('Deals'!$A$2:$A$1000)=Assumptions!$B$1)
)

One practical note: use bounded ranges rather than full-column references inside SUMPRODUCT. $A$2:$A$1000 instead of $A:$A. On a 5,000-row CRM export, SUMPRODUCT on an unbounded column recalculates every blank cell too, which adds up. On a bounded range it's fast - typically under half a second for a dataset that size.

SUMPRODUCT also handles conditions that don't fit neatly into date range notation. Counting Q4 closings across any year:

=SUMPRODUCT(
  (MONTH('Deals'!$A$2:$A$1000)>=10) *
  (MONTH('Deals'!$A$2:$A$1000)<=12)
)

Pattern 3: ARRAYFORMULA + COUNTIFS + SEQUENCE

This is the one worth knowing for board packs. COUNTIFS (plural, unlike COUNTIF) does work with ARRAYFORMULA when you feed it an array of criteria values. Combined with SEQUENCE, one formula spills 12 monthly counts:

=ARRAYFORMULA(
  COUNTIFS(
    'Deals'!$A$2:$A$1247, ">="&DATE(Assumptions!$B$1, SEQUENCE(12), 1),
    'Deals'!$A$2:$A$1247, "<"&DATE(Assumptions!$B$1, SEQUENCE(12)+1, 1)
  )
)

Place this in B2 of your Summary tab and it spills January through December counts downward. Change the year in Assumptions!$B$1 and all 12 rows rebuild instantly.

SEQUENCE(12) generates {1;2;3;...;12} as a vertical array. ARRAYFORMULA tells COUNTIFS to evaluate each element separately, producing 12 separate counts stacked in a column. DATE(year, 13, 1) correctly resolves to January 1 of the following year, so December handles without a special case.

Pairing COUNTIFS and SUMIFS with Identical Date Logic

The real payoff of Pattern 3 is that SUMIFS shares the same SEQUENCE structure. If your board pack needs both monthly deal counts and monthly new ARR side by side, both formulas use identical date bounds:

=ARRAYFORMULA(
  COUNTIFS(
    'CRM Export'!$A$2:$A$1247, ">="&DATE(Assumptions!$B$2, SEQUENCE(12), 1),
    'CRM Export'!$A$2:$A$1247, "<"&DATE(Assumptions!$B$2, SEQUENCE(12)+1, 1),
    'CRM Export'!$E$2:$E$1247, "Enterprise"
  )
)

=ARRAYFORMULA(
  SUMIFS(
    'CRM Export'!$C$2:$C$1247,
    'CRM Export'!$A$2:$A$1247, ">="&DATE(Assumptions!$B$2, SEQUENCE(12), 1),
    'CRM Export'!$A$2:$A$1247, "<"&DATE(Assumptions!$B$2, SEQUENCE(12)+1, 1),
    'CRM Export'!$E$2:$E$1247, "Enterprise"
  )
)

One assumption cell drives both columns. That's the kind of structure that survives a fiscal year rollover without a 20-minute formula audit.

The Hidden Reason COUNTIFS Returns 0 When You Know There's Data

If you've built the COUNTIFS correctly and still get 0, your date column probably contains text strings formatted to look like dates, not real date values. Google Sheets stores actual dates as serial numbers; COUNTIFS date comparisons only work against true date values. Text-formatted dates - common in Salesforce exports and NetSuite downloads - look identical in the cell but fail silently in any comparison formula.

Check by selecting a date cell. If the value is left-aligned, it's text. If it's right-aligned, it's a real date. Fix it with =ARRAYFORMULA(DATEVALUE(A2:A1000)) across the column, or use Data > Split text to columns on the affected range to trigger Google's auto-conversion.

As of May 2026, Google Sheets doesn't auto-convert text-formatted dates inside COUNTIFS criteria comparisons. This limitation hits finance teams most often on raw CRM or ERP exports, where date formatting consistency depends entirely on whoever configured the export.

For a deeper look at COUNTIF by month without the ARRAYFORMULA angle, see COUNTIF by Month in Google Sheets.

If building these multi-criteria monthly formulas by hand gets tedious across a 12-tab model, ModelMonkey's AI assistant can generate the COUNTIFS + SEQUENCE structure from a plain description of your summary layout. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.