Google Sheets COUNTIF ARRAYFORMULA Month Range

For example, if Assumptions!$B$3 contains 9/1/2026, this counts September bookings in a $4.2M annual-plan model:

=COUNTIFS(
  'Bookings'!$B$2:$B, ">=" & Assumptions!$B$3,
  'Bookings'!$B$2:$B, "<" & EDATE(Assumptions!$B$3, 1),
  'Bookings'!$F$2:$F, "Closed Won"
)

The < next-month boundary matters. It includes 9/30/2026 23:59:59 without relying on an end-of-day timestamp, which is where otherwise sensible formulas quietly drop late-day CRM records.

Google documents COUNTIF as a single-criterion counting function and COUNTIFS as its multi-criterion counterpart. Google also notes that ARRAYFORMULA expands results into multiple cells, not that it turns every aggregation into a row-by-row calculation. Google COUNTIF documentation and Google COUNTIFS documentation are unusually clear on that distinction.

Why COUNTIF and ARRAYFORMULA usually fail for a month range

The tempting formula is something like this:

=ARRAYFORMULA(COUNTIF('Bookings'!$B$2:$B, ">=" & B$4))

It returns a number, but it does not define the end of the month. Worse, wrapping it in ARRAYFORMULA does not make COUNTIF calculate a separate answer for each month in B4:M4. You have one aggregate function being asked to spill 12 aggregates. Sheets generally gives you one answer, or a result that looks plausible enough to survive until the quarterly board pack.

A month is a range, not a criterion. It needs both boundaries:

">=" & month_start
"<" & EDATE(month_start, 1)

Use EOMONTH(month_start, 0) only when you are certain the source column stores dates with no times. In operating data from HubSpot, Stripe, payroll, or an ERP export, timestamps are common. The next-month boundary is safer.

Google Sheets COUNTIF ARRAYFORMULA month range: when it works

COUNTIF plus ARRAYFORMULA works when the task is not an aggregate-by-month calculation, but a row-level flag. Build a monthly inclusion flag on the source tab, then sum it elsewhere.

Suppose Bookings!B:B holds close dates and Assumptions!$B$3 is the month start. This returns 1 for every September closed-won booking and 0 otherwise:

=ARRAYFORMULA(
  IF(
    B2:B="",
    ,
    --(
      (B2:B>=Assumptions!$B$3) *
      (B2:B<EDATE(Assumptions!$B$3,1)) *
      (F2:F="Closed Won")
    )
  )
)

That is useful when the model needs an auditable helper column for a contribution-margin-by-SKU bridge or a bank-syndicate DCF data pull. It is not the cleanest way to produce the 12 monthly cells in a reporting output.

Google’s ARRAYFORMULA documentation describes it as a way to display values across multiple rows or columns. That is exactly what the helper-column approach does.

The dependable monthly formula for an FP&A model

For a row of calendar-month headers in Returns Analysis!C$4:N$4, use this formula in C18 and copy across:

=COUNTIFS(
  'P&L Detail'!$C$2:$C, ">=" & C$4,
  'P&L Detail'!$C$2:$C, "<" & EDATE(C$4, 1),
  'P&L Detail'!$G$2:$G, $B18
)

If B18 contains Enterprise, the formula returns the monthly count for that segment. The same month boundary can anchor a revenue roll-forward:

=SUMIFS(
  'P&L'!C:C,
  'P&L'!B:B, ">=" & Assumptions!$B$3,
  'P&L'!B:B, "<" & EDATE(Assumptions!$B$3,1)
)

For a September close review, this could tie 286 closed-won bookings to $350,000 of recognized revenue at 38.5% gross margin. The count is not the revenue calculation, but it is often the fastest control check when a 14.2x EBITDA exit case suddenly moves because a date filter changed.

COUNTIFS versus helper columns versus QUERY

The non-obvious point is model maintenance. A QUERY can produce a tidy monthly table, but a row of COUNTIFS tied to model-period headers usually survives a new forecast year, scenario copy, or changed accounting calendar with less repair work. In a 10,000-row operating-data tab, that matters more than saving 1 formula.

Google Sheets supports up to 10 million cells per spreadsheet, according to Google’s file-size limits. That is plenty of room to hide a performance problem in whole-column references. In a high-traffic model, cap ranges at the expected data volume, such as $B$2:$B$50000, rather than recalculating against 1,000,000 rows every time someone changes an assumption.

A calendar trap that changes the answer

If C$4 displays Sep-26 but is text rather than a real date, the formula can return 0 without an obvious error. Test it with:

=ISNUMBER(C$4)

It must return TRUE. Better still, set monthly headers with a formula such as:

=EDATE(Assumptions!$B$3, COLUMN()-COLUMN($C$4))

Then format the row as mmm-yy. You have real dates driving the month windows, not labels impersonating dates.

As of September 2026, this is still the cleanest pattern for monthly date-based counts in Sheets: real month-start dates, >= for the opening boundary, and < EDATE(month_start,1) for the closing boundary. It also transfers neatly from a quarterly board pack to runway sensitivity on new-hire pace, because both depend on periods that must reconcile across tabs.

If you inherit a model where the date logic is tangled across P&L, Cash Flow, and a raw CRM dump, ModelMonkey can inspect the linked ranges and surface the month filters that do not use the same boundaries. Choose a plan or credit pack - it works in both Google Sheets and Excel.