Chapter 17 · 17.3
Adding up sales per month or per period with SUMIFS
A cash book, sales list or timesheet has a date on every row. What you want to know is the total per month, per quarter or between two dates, and perhaps per product as well. With SUMIFS and two date conditions, you can build that kind of summary, and it stays correct when new rows are added. A pivot table can do this too (see A pivot table by month, quarter or week), but SUMIFS is more practical when the summary needs to keep a fixed layout, for example for a report or a budget.
As an example, we use the sales of Bloemisterij Het Zonnetje on the Sales worksheet: the date in A, the product in B, the category in C and the amount in D, from row 2 to row 300.
Step 1: a column of months
Create a worksheet called Summary. Instead of typing twelve dates, you can create them all with one formula. Type in A2:
=EDATE(DATE(2026,1,1),SEQUENCE(12,1,0))
SEQUENCE(12,1,0) creates the numbers 0 to 11, and EDATE adds that many months to 1 January 2026. You get the first day of each month, from A2 to A13. Give A2:A13 the custom format mmmm yyyy (via More formats, Custom, see Custom number formats: "12 pcs", amounts in thousands, red for negatives), and the cells show January 2026, February 2026 and so on.

Step 2: sales per month
Type in B2:
=SUMIFS(Sales!$D$2:$D$300,Sales!$A$2:$A$300,">="&$A2,Sales!$A$2:$A$300,"<="&EOMONTH($A2,0))
SUMIFS adds up the amounts where the date is greater than or equal to the first day of the month and less than or equal to the last day (EOMONTH with 0 returns the end of the same month). Drag the formula down to B13, or select B2:B13 before you type and finish with Ctrl+Enter.
Step 3: per product
Put product names side by side as headings, for example Rozen in C1, Tulpen in D1 and Boeket in E1, and type in C2:
=SUMIFS(Sales!$D$2:$D$300,Sales!$A$2:$A$300,">="&$A2,Sales!$A$2:$A$300,"<="&EOMONTH($A2,0),Sales!$B$2:$B$300,C$1)
This adds one more pair of conditions: the product must match the heading. Pay attention to the dollar signs: $A2 locks the column of months, C$1 locks the row of headings. That way, the same formula works across the whole range. Quick tip: first select C2:E13, type the formula and press Ctrl+Enter. Euro-Office then enters the formula in all selected cells, with the references adjusted for each cell.

Counting instead of adding up
To count the number of sales per month, use the same conditions with COUNTIFS:
=COUNTIFS(Sales!$A$2:$A$300,">="&$A2,Sales!$A$2:$A$300,"<="&EOMONTH($A2,0))
Any period you like
Put a start date in H1 and an end date in I1. The sales in between, including both days:
=SUMIFS(Sales!D:D,Sales!A:A,">="&H1,Sales!A:A,"<="&I1)
Pitfalls
- Quotation marks and &: the comparison goes between quotation marks and the cell follows it with &: ">="&H1. If you type ">=H1", Euro-Office looks for the text H1 and the result is 0.
- Dates that include a time (16/11/2026 14:32) on the last day of the month fall outside "<="&EOMONTH(...), because that is midnight. In that case, use "<"&EDATE($A2,1) as the second condition: less than the first day of the next month.
- Filtering on the month number only, for example with MONTH() in a helper column, adds January 2025 and January 2026 together. Always include the year.
- Dates stored as text are not counted. See Dates that come in as text or have day and month swapped.
