How to Calculate Average Order Value by Month in Google Sheets

Revenue divided by the number of orders, month by month. The trap is that AVERAGE() looks right and is not.

The short answer: average order value is revenue divided by distinct orders, =SUMIFS(...)/COUNTUNIQUEIFS(...). In the sample data, October 2026's AOV is $611.48. AVERAGE on the same rows says $326.03, because it averages product lines, not 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: revenue over distinct orders

Headings in row 1: Month, Revenue, Orders, Avg order value. Months in column A from =DATE(2026,5,1) down with =EDATE(A2,1). Then in row 2, copied down:

=SUMIFS(Orders!$J:$J, Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1))
=COUNTUNIQUEIFS(Orders!$B:$B, Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1))
=IFERROR(B2/C2, "")

B is the month's revenue, C counts each order ID once, and D divides them. Format B and D as currency.

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

Why not AVERAGE? An order with three products is three rows. =AVERAGEIFS(Orders!J:J, Orders!A:A, ">="&DATE(2026,10,1), Orders!A:A, "<"&DATE(2026,11,1)) returns $326.03 on the sample data: the average line, well below the real $611.48. It is only right when every order is a single row.

Gotchas

• Line-level data. If your export repeats the order ID on every product line, always count distinct IDs. If it is one row per order already, COUNTIFS is fine.
• What counts as revenue. Decide whether AOV includes shipping, tax and discounts, then keep it the same every month. Shopify's "average order value" and a total-sales column divided by orders rarely match for this reason.
• Refunded and test orders. A $0 test order or a fully refunded order still counts as an order and drags AOV down. Filter them out first.
• Small months. One big order can move AOV a lot in a quiet month. Look at the order count beside it before reading too much into a jump.

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("average order value by month from May to October 2026, with revenue and order count", "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.

Want one number instead of a table? The "short" mode returns a single value you can format and point other formulas at:

=QUIRIZ("average order value in October 2026", "short")

The cell holds 611.48; formatted as currency it reads $611.48.

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

• Monthly sales report
• New vs returning customers
• Top customers by revenue
• Month-over-month growth
• How to use SUMIF and COUNTIF in Google Sheets
• How to remove duplicates in Google Sheets
• Net sales vs gross sales
• All Google Sheets questions

Frequently asked questions