COUNTIF ARRAYFORMULA MONTH in Google Sheets

=COUNTIFS(
  Data!$A$2:$A$6, ">="&C2,
  Data!$A$2:$A$6, "<"&EDATE(C2,1)
)

Here, C2 contains the first day of the month, such as January 1, 2026. The formula includes that date but excludes the first day of the following month. As a result, it handles different month lengths, distinguishes between years, and includes timestamps on the final day.

Why COUNTIF with ARRAYFORMULA(MONTH()) is unreliable

You might try:

=COUNTIF(ARRAYFORMULA(MONTH(Data!A2:A6)),1)

Google documents the first argument of COUNTIF as a range. However, MONTH(Data!A2:A6) produces a calculated array rather than a worksheet range. Although ARRAYFORMULA expands the MONTH calculation, it doesn't reliably turn the result into the standard range that COUNTIF expects.

This month-number method also discards the year. For example, testing for month 1 can combine January 2025, January 2026, and January 2027.

Date boundaries avoid both issues:

• >= includes the first day of the selected month.
• <EDATE(month_start,1) excludes the next month.
• The year remains part of the comparison.
• Date-times on the final day remain inside the selected period.

Google documents COUNTIFS as a series of range-and-criterion pairs. Meanwhile, EDATE returns a date a specified number of months before or after another date.

Reproducible monthly count example

Create two tabs named Data and Summary. The following values are illustrative.

Set up the Data tab

Enter these values:

The leading apostrophe in A6 forces the entry to remain text. Although it displays as 2026-01-15, it isn't stored as a numeric date value.

Format A2:A4 as dates or date-times.

Set up the Summary tab

Enter:

In Summary!D2, enter:

=COUNTIFS(
  Data!$A$2:$A$6, ">="&C2,
  Data!$A$2:$A$6, "<"&EDATE(C2,1)
)

Copy the formula to D3.

The expected results are:

January contains the numeric date in A2 and the date-time in A3, while February contains A4. Neither the blank in A5 nor the text value in A6 meets the numeric boundaries.

For a manual check, filter Data!A2:A6 for values from January 1, 2026 inclusive to February 1, 2026 exclusive. Exactly two numeric date values should remain.

Generate recurring monthly counts with MAP

For a recurring report, keep numeric month-start dates in Summary!C2:C3. Clear D2:D3, then enter this formula in D2:

=MAP(
  C2:C3,
  LAMBDA(m,
    COUNTIFS(
      Data!$A$2:$A$6, ">="&m,
      Data!$A$2:$A$6, "<"&EDATE(m,1)
    )
  )
)

MAP applies the boundary formula to each month in the list. The expected vertical result is:

2
1

Next, add =DATE(2026,3,1) to C4 and extend the MAP input to C2:C4. With no March records, the expected result becomes {2;1;0}.

The cells below the MAP formula must be empty so the results can expand. For a horizontal report, wrap the formula in TRANSPOSE.

For more about array behavior, see ARRAYFORMULA in Google Sheets and ARRAYFORMULA with COUNTIF.

Handle blanks, text dates, and timestamps

Blank cells

The boundary-based COUNTIFS formula doesn't count blank cells because they don't meet the date criteria. If you clear every cell in Data!A2:A6, the result should be 0.

A cell containing spaces isn't technically blank. Because it's text, it won't meet the numeric boundaries.

Text-formatted dates

Formatting text as a date changes its appearance, not its underlying value. To check whether a cell contains a number, use:

=ISNUMBER(Data!A6)

The illustrative A6 returns FALSE and won't count until it's converted.

Although ISNUMBER excludes text and blanks, it doesn't prove that a number is a valid business date. Google Sheets stores dates as serial numbers, so an unrelated numeric value could also satisfy the date boundaries. If unrelated numbers may enter the column, use date validation or reasonable minimum and maximum dates.

When the text matches the spreadsheet locale, enter this conversion formula in Data!B6:

=IF(
  LEN(TRIM(Data!A6))=0,
  "",
  DATEVALUE(TRIM(Data!A6))
)

The formula returns blank for an empty cell or text containing only ordinary spaces. It converts the sample text date into a numeric date value. Malformed nonblank text remains visible as an error, allowing it to be corrected instead of silently converted to zero.

However, TRIM doesn't remove every whitespace character. Imported data can contain non-breaking spaces, represented by CHAR(160). Normalize those explicitly before testing or converting:

=LET(
  cleaned, TRIM(SUBSTITUTE(Data!A6,CHAR(160)," ")),
  IF(LEN(cleaned)=0,"",DATEVALUE(cleaned))
)

After reviewing the conversion, use the cleaned date column as the source range or replace the original text.

Dates containing times

A criterion such as <=January 31, 2026 can exclude records from later that day because a stored date-time includes a time fraction. Instead, use this upper boundary:

"<"&EDATE(C2,1)

It includes every timestamp before midnight on the first day of the next month. In the example, Data!A3 at 18:30 on January 31 remains part of January.

SUMPRODUCT and FILTER alternatives

Use SUMPRODUCT when the conditions are expressed as array calculations:

=SUMPRODUCT(
  --ISNUMBER(Data!$A$2:$A$6),
  --(Data!$A$2:$A$6>=C2),
  --(Data!$A$2:$A$6<EDATE(C2,1))
)

The ISNUMBER guard excludes blanks and text-formatted dates. It doesn't reject unrelated numbers that fall within the selected serial-number boundaries, so use a controlled date column or add appropriate domain bounds when that risk exists.

For the defined January fixture, the expected result is 2.

If you need to inspect the records behind the count, use FILTER:

=FILTER(
  Data!$A$2:$A$6,
  ISNUMBER(Data!$A$2:$A$6),
  Data!$A$2:$A$6>=C2,
  Data!$A$2:$A$6<EDATE(C2,1)
)

The expected rows are Data!A2 and Data!A3. Unlike a counting formula, FILTER returns the matching records.

When no records match, COUNTIFS and SUMPRODUCT return 0. A bare FILTER, by contrast, returns #N/A. Wrapping it as =IFNA(FILTER(...),"") produces one blank output cell, not a zero-row array.

Validation checks

Reset the illustrative fixture before each independent check.

These checks follow from the defined inputs. They aren't results from an executed or benchmarked workbook.

Common formula errors

Formula parse error: These examples use comma argument separators. If your Google Sheets locale requires semicolons, replace the commas with semicolons.

Wrong year included: A MONTH(range)=1 test matches January in every year. Use full date boundaries tied to a month-start cell.

Valid-looking dates aren't counted: Use ISNUMBER to determine whether a cell contains a number rather than text. Applying date formatting doesn't convert text into a stored date value.

Unrelated numbers are counted: ISNUMBER doesn't distinguish date serials from other numbers. Restrict the source column to dates or apply reviewed minimum and maximum date bounds.

Month input is text: Store the first day of the month in the input cell. Apply the custom number format MMM-YY if you want a compact display.

Array result wasn't expanded: Clear the cells beneath the MAP formula.

Different range sizes: Every range used in SUMPRODUCT or FILTER must cover the same rows.

Adapt the formula to an existing model

In Google Sheets, ModelMonkey can inspect the active spreadsheet, sheet, and range context, then help adapt the boundary formula to your date column and month inputs. ModelMonkey also supports spreadsheet assistance in Excel, but the active-context behavior described here is specific to the Google Sheets integration.

Review any proposed formula and reconcile its counts with the underlying records.

Install ModelMonkey to use it with this workflow.