FORMULA & SQL / TOTALS
Count rows for each month.
Tested, not guessed.
The Excel and Google Sheets formula and the SQL query, each run on the rows below. Open this with your own data ↗
The example rows
| Row | A: Region | B: Rep | C: Amount | D: Order Date | E: Status | F: Customer |
|---|---|---|---|---|---|---|
| 2 | North | Ana | 1,200 | 2026-03-01 | Paid | Jo Smith |
| 3 | South | Ben | 450 | 2026-03-15 | Open | Raj Patel |
| 4 | North | Cy | 300 | 2026-04-02 | Paid | Lee Wong |
| 5 | East | Ana | 980.50 | 2026-04-20 | Paid | Mia Ross |
| 6 | North | Ben | 75 | 2026-05-05 | Open | Jo Smith |
| 7 | West | Dee | 1,640 | 2026-05-18 | Paid | Sam Okoro |
The formula
Excel
=COUNTIFS(D:D,">="&EOMONTH(D2,-1)+1,D:D,"<="&EOMONTH(D2,0))
The number of rows where the Order Date column (D:D) is at least the last day of the month minus 1 months from Order Date (D2) plus 1 and the Order Date column (D:D) is at most the last day of the month 0 months from Order Date (D2).
Gives each row the count for its month.
Run on the example rows:
| Row | Result |
|---|---|
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
The SQL
PostgreSQL
SELECT DATE_TRUNC('month', order_date) AS month_start, COUNT(*) AS row_count
FROM sales
GROUP BY 1
ORDER BY 1Run on the example rows:
| month_start | row_count |
|---|---|
| 2026-03-01 | 2 |
| 2026-04-01 | 2 |
| 2026-05-01 | 2 |
Other databases
MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month_start, COUNT(*) AS row_count FROM sales GROUP BY 1 ORDER BY 1
SQL Server
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start, COUNT(*) AS row_count FROM sales GROUP BY 1 ORDER BY 1
SQLite
SELECT strftime('%Y-%m-01', order_date) AS month_start, COUNT(*) AS row_count
FROM sales
GROUP BY 1
ORDER BY 1BigQuery
SELECT DATE_TRUNC(order_date, MONTH) AS month_start, COUNT(*) AS row_count FROM sales GROUP BY 1 ORDER BY 1
Snowflake
SELECT DATE_TRUNC('month', order_date) AS month_start, COUNT(*) AS row_count
FROM sales
GROUP BY 1
ORDER BY 1Your own data
Paste your rows into the free tool and it writes this for your columns, then runs it on your data before you copy it. Open the tool ↗