How to Make a Monthly Sales Report in Google Sheets

Revenue and orders for each of the last twelve months, in a table that updates itself. Two formulas, copied down twelve rows.

The short answer: list the first day of each month in column A, then =SUMIFS(Orders!$J:$J, Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1)) totals that month's revenue. In the sample data, the twelve months from November 2025 to October 2026 add up to $1,572,281 across 2,547 orders.

The sample data

Sample data: a tab named Orders with 8,120 order lines from January 2025 to October 2026, one row per order line. Revenue is Qty × Unit price; Cost is the line's cost of goods (Qty × unit cost). Download orders.csv and import it (File → Import) to follow along. The business is fictional.

The classic way: a month column plus SUMIFS

On a new tab, put headings in row 1 (Month, Revenue, Orders). Then fill in the months: =DATE(2025,11,1) in A2 and =EDATE(A2,1) in A3, copied down to A13. Format column A as a month (Format → Number → Custom date and time → "MMM yyyy").

Revenue for each month, in B2 and copied down:

=SUMIFS(Orders!$J:$J, Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1))

Number of orders, in C2 and copied down. Each order has several lines, so count distinct order IDs rather than rows:

=COUNTUNIQUEIFS(Orders!$B:$B, Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1))

A Total row underneath is =SUM(B2:B13) and =SUM(C2:C13). Using "on or after the 1st, before the 1st of next month" catches every day of the month, including the 31st and any rows with a time on them.

Computed from the sample CSV above. Cells formatted as currency and percent.

Make it roll. To always show the last twelve full months, replace A2 with =EDATE(EOMONTH(TODAY(),-1)+1, -12). That is the first day of this month, minus twelve months. Everything below recalculates on its own.

Prefer one QUERY? This returns year, month and revenue in one go:

=QUERY(Orders!A:K, "select year(A), month(A)+1, sum(J) where A >= date '2025-11-01' and A < date '2026-11-01' group by year(A), month(A)+1 order by year(A), month(A)+1 label year(A) 'Year', month(A)+1 'Month', sum(J) 'Revenue'", 1)

Note the +1: QUERY's month() counts January as 0. Forget it and every month is labeled one too early.

Gotchas

• Counting rows instead of orders. COUNTIFS on order lines counts products, not orders. COUNTUNIQUEIFS on the Order ID column counts each order once.
• The current month is partial. A rolling report that includes this month always shows it low. The rolling start above stops at last month on purpose.
• Dates with times. "<="&EOMONTH(A2,0) misses anything after midnight on the last day. The "<"&EDATE(A2,1) form does not.
• Text dates. If SUMIFS returns 0 everywhere, column A of Orders is text. Convert it to real dates first.

The one-cell way: =QUIRIZ

With the Quiriz add-on for Google Sheets, you type the question instead of building the formula. =QUIRIZ does not read the cells around it: it answers from the datasets in your Quiriz account. For this example, upload orders.csv as a dataset, or connect your Shopify store. You can also add a Google Sheet itself as a dataset that refreshes on a schedule. Then, in any empty cell:

=QUIRIZ("revenue and number of orders by month from November 2025 to October 2026, with a total row", "table")

The "table" mode spills the answer down and to the right from that cell, header row first:

Same figures as the formula result above, from the same sample data. The spilled cells are plain numbers, so you can format them, total them or chart them; here they are formatted as currency and percent. Column headings are written with each answer and may be worded differently.

The other modes: "verbose" (the default) answers in a sentence, "bullets" writes two to four points one per cell, and "notes" adds how the figure was calculated. Install Quiriz for Google Sheets and try the question on your own data.

Related questions

• Month-over-month growth
• Year-over-year comparison
• Average order value by month
• Sales by region and channel
• How to use SUMIF and COUNTIF in Google Sheets
• How to use the QUERY function in Google Sheets
• How to write a monthly business report
• All Google Sheets questions

Frequently asked questions