How to Build a Monthly Sales Summary with SUMIFS (No Pivot Table)

Written with AI assistance. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).

Pivot tables are great until you need the summary to sit in a fixed layout, update the moment a row is added, or be readable by someone who's never clicked "Refresh". A small SUMIFS table does the same job in plain formulas you can audit cell by cell.

Everything here uses standard functions available in both Excel and Google Sheets (SUMIFS, COUNTIFS, EDATE, SUM, SUMPRODUCT, ISNUMBER), with no macros.

1. The order list

One row per order, from row 5, columns A–D: Date, Product, Amount, Status (Completed or Refunded). Use a dropdown for Status so it's always spelled the same way.

2. The month column

In G5, type the first day of your first month, e.g. 1 Jan 2026. Below it (G6, fill down for as many months as you need):

=EDATE(G5,1)

EDATE adds whole months, so you always land on the 1st, even after February. Format the column as mmm yyyy so it reads "Jan 2026" while staying a real date underneath.

3. Sales and orders per month

Sales (H5, fill down):

=SUMIFS($C$5:$C$204,$A$5:$A$204,">="&G5,$A$5:$A$204,"<"&EDATE(G5,1),$D$5:$D$204,"Completed")

Orders (I5):

=COUNTIFS($A$5:$A$204,">="&G5,$A$5:$A$204,"<"&EDATE(G5,1),$D$5:$D$204,"Completed")

The window is "on or after the 1st, and before the 1st of next month". That's why 31 January counts in January and 1 February counts in February. It also stays right if your dates ever carry a time of day, which a <= end-of-month test would get wrong.

4. Average order, refunds, running total, change

5. The check cells (the part pivot tables don't give you)

All completed sales in the list (H12):

=SUMIFS($C$5:$C$204,$D$5:$D$204,"Completed")

Check (H13):

=IF(ROUND(SUM(H5:H10)-H12,2)=0,"OK","CHECK")

If the months don't add up to the whole list, some sales fell outside every month. There are two usual reasons, so count both:

Dates typed as text (H14). These look like dates but no date test can match them:

=SUMPRODUCT(($A$5:$A$204<>"")*NOT(ISNUMBER($A$5:$A$204)))

Dates outside your months (H15):

=COUNTIFS($A$5:$A$204,"<"&G5)+COUNTIFS($A$5:$A$204,">="&EDATE(G10,1))

In my sample list I planted one of each: a date pasted as the text 2026-03-15, and an order dated 1 July in a January–June summary. The check showed CHECK, and each counter showed 1. Retype the text date as a real date and extend the months, and it goes back to OK.

6. What the sample came out as

Fourteen made-up orders, Jan–Jun 2026:

March shows only 2 orders because the text-date order on 15 March was skipped, which is exactly what the check cell is there to catch. These are demo numbers, not anyone's real sales.

7. Test it before you trust it

Add rows you know the answer for, and confirm each lands where it should:

• the last day of a month and the first day of the next
• a refunded order (goes to Refunds, not Sales)
• a month with no orders (shows 0 and a blank average, not an error)
• one date typed as text, which should trip the check

Then delete them and use it with real data.

Prefer a ready-made file? There's a free Order & Payment Tracker (.xlsx workbook, no macros). You log each order once, and it works out what's still owed and whether it's overdue, by customer and in a one-page summary: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker

About me, and a disclosure: I'm Ashish, based in Kolkata, with 18+ years of work experience, and I write these tutorials and build my workbooks with AI assistance under my direction. The free tracker is mine.