Combination
Revenue per month for twelve months in one formula
Total revenue for each of twelve months with one SUMIFS: SEQUENCE counts the months, EDATE turns each count into the first day of a month, and those dates become the criteria.
Functions used:SUMIFSEDATESEQUENCEExcel 2021 or later (This combination needs Excel 2021 or later)
Difficulty rating 4 of 5
Worked example
Revenue per month for the analytical review
Scenario
For the analytical review of Veldhuis Installatie BV you have eight revenue postings on account 8000 from the XAF auditfile for 2025. You want the revenue for each of the twelve months in one column, to compare with the budget and spot months without revenue, without writing twelve SUMIFS formulas.
The data
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Date | Entry no. | Revenue | First month | 1 Jan 2025 | ||
| 2 | 15 Jan 2025 | 25-0101 | €12,500.00€ 12.500,00 | Revenue per month | €20,900.00€ 20.900,00=SUMIFS(C2:C9, A2:A9, ">="&EDATE(F1, SEQUENCE(12, 1, 0)), A2:A9, "<"&EDATE(F1, SEQUENCE(12, 1, 1)))=SUMIFS(C2:C9; A2:A9; ">="&EDATE(F1; SEQUENCE(12; 1; 0)); A2:A9; "<"&EDATE(F1; SEQUENCE(12; 1; 1))) | ||
| 3 | 28 Jan 2025 | 25-0118 | €8,400.00€ 8.400,00 | €15,200.00€ 15.200,00 | |||
| 4 | 10 Feb 2025 | 25-0204 | €15,200.00€ 15.200,00 | €0.00€ 0,00 | |||
| 5 | 3 Apr 2025 | 25-0407 | €9,800.00€ 9.800,00 | €16,100.00€ 16.100,00 | |||
| 6 | 30 Apr 2025 | 25-0433 | €6,300.00€ 6.300,00 | €0.00€ 0,00 | |||
| 7 | 21 Jul 2025 | 25-0712 | €21,000.00€ 21.000,00 | €0.00€ 0,00 | |||
| 8 | 14 Nov 2025 | 25-1109 | €17,500.00€ 17.500,00 | €21,000.00€ 21.000,00 | |||
| 9 | 31 Dec 2025 | 25-1243 | €11,400.00€ 11.400,00 | €0.00€ 0,00 | |||
| 10 | €0.00€ 0,00 | ||||||
| 11 | €0.00€ 0,00 | ||||||
| 12 | €17,500.00€ 17.500,00 | ||||||
| 13 | €11,400.00€ 11.400,00 | ||||||
| 14 |
Swipe sideways to see all columns
Formula in F2
=SUMIFS(C2:C9,A2:A9, ">="&EDATE(F1, SEQUENCE(12, 1, 0)), A2:A9, "<"&EDATE(F1, SEQUENCE(12, 1, 1)))
=SUMIFS(C2:C9;A2:A9; ">="&EDATE(F1; SEQUENCE(12; 1; 0)); A2:A9; "<"&EDATE(F1; SEQUENCE(12; 1; 1)))
SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Read more about SUMIFSPress Escape to close
sum_rangeThe cells whose numbers are added when a row passes every test.Revenue column, 8 cellsPress Escape to close
criteria_range1The range the first condition is tested on; same size as sum_range.Date column, 8 cellsPress Escape to close
criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".">=": comparisonsPress Escape to close
&Joins textPress Escape to close
EDATE ZELFDE.DAGThe same day of the month, a number of months away.Read more about EDATEPress Escape to close
start_dateThe date to count from; its day number is kept where the month allows.Input cell: 1 Jan 2025Press Escape to close
SEQUENCE REEKSGenerates a list of numbers in steps.Used here as: monthsMonths to move: 12 is a year later, -3 is three months back.Read more about SEQUENCEPress Escape to close
rowsHow many rows the block has.Press Escape to close
columns (optional)How many columns; default 1.Press Escape to close
start (optional)The first number; default 1.Press Escape to close
criteria_range2 (optional)Range for a further condition; pairs repeat up to 127 times.Date column, 8 cellsPress Escape to close
criteria2 (optional)The test for criteria_range2, written like criteria1.Press Escape to close
&Joins textPress Escape to close
EDATE ZELFDE.DAGThe same day of the month, a number of months away.Read more about EDATEPress Escape to close
start_dateThe date to count from; its day number is kept where the month allows.Input cell: 1 Jan 2025Press Escape to close
SEQUENCE REEKSGenerates a list of numbers in steps.Used here as: monthsMonths to move: 12 is a year later, -3 is three months back.Read more about SEQUENCEPress Escape to close
rowsHow many rows the block has.Press Escape to close
columns (optional)How many columns; default 1.Press Escape to close
start (optional)The first number; default 1.Press Escape to close
Result: €20,900.00 / €15,200.00 / €0.00 / €16,100.00 / €0.00 / €0.00 / €21,000.00 / €0.00 / €0.00 / €0.00 / €17,500.00 / €11,400.00€ 20.900,00 / € 15.200,00 / € 0,00 / € 16.100,00 / € 0,00 / € 0,00 / € 21.000,00 / € 0,00 / € 0,00 / € 0,00 / € 17.500,00 / € 11.400,00
How the pieces fit together
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=SEQUENCE(12,
1, 0) =SEQUENCE(12;
1; 0) rowsHow many rows the block has.Press Escape to closecolumns (optional)How many columns; default 1.Press Escape to closestart (optional)The first number; default 1.Press Escape to closeA column of twelve numbers that starts at 0 and goes up by 1: 0, 1, 2, up to 11. These are the months to move forward from the start date.
=EDATE(F1,
SEQUENCE(12, 1, 0)) =EDATE(F1;
SEQUENCE(12; 1; 0)) EDATE ZELFDE.DAGThe same day of the month, a number of months away.Read more about EDATEPress Escape to closestart_dateThe date to count from; its day number is kept where the month allows.Input cell: 1 Jan 2025Press Escape to closeSEQUENCE REEKSGenerates a list of numbers in steps.Used here as: monthsMonths to move: 12 is a year later, -3 is three months back.Read more about SEQUENCEPress Escape to closerowsHow many rows the block has.Press Escape to closecolumns (optional)How many columns; default 1.Press Escape to closestart (optional)The first number; default 1.Press Escape to closeEDATE moves the start date in
F1(1 January 2025) forward by each of the twelve numbers. The result is twelve dates: 1 January 2025, 1 February 2025, up to 1 December 2025. These are the first days of the months.=EDATE(F1,
SEQUENCE(12, 1, 1)) =EDATE(F1;
SEQUENCE(12; 1; 1)) EDATE ZELFDE.DAGThe same day of the month, a number of months away.Read more about EDATEPress Escape to closestart_dateThe date to count from; its day number is kept where the month allows.Input cell: 1 Jan 2025Press Escape to closeSEQUENCE REEKSGenerates a list of numbers in steps.Used here as: monthsMonths to move: 12 is a year later, -3 is three months back.Read more about SEQUENCEPress Escape to closerowsHow many rows the block has.Press Escape to closecolumns (optional)How many columns; default 1.Press Escape to closestart (optional)The first number; default 1.Press Escape to closeThe same, but starting at 1 instead of 0: 1 February 2025 up to 1 January 2026. Each date is the first day of the next month, so it is the end of the month before it, exclusive.
=">="&EDATE(F1,
SEQUENCE(12, 1, 0)) =">="&EDATE(F1;
SEQUENCE(12; 1; 0)) TextLiteral valuePress Escape to close&Joins textPress Escape to closeEDATE ZELFDE.DAGThe same day of the month, a number of months away.Read more about EDATEPress Escape to closestart_dateThe date to count from; its day number is kept where the month allows.Input cell: 1 Jan 2025Press Escape to closeSEQUENCE REEKSGenerates a list of numbers in steps.Used here as: monthsMonths to move: 12 is a year later, -3 is three months back.Read more about SEQUENCEPress Escape to closerowsHow many rows the block has.Press Escape to closecolumns (optional)How many columns; default 1.Press Escape to closestart (optional)The first number; default 1.Press Escape to closePuts the operator in front of every date, so you get twelve criteria. Excel joins the operator to the date serial number, so the first one is
>=45658(1 January 2025). SUMIFS reads this as: on or after that date.=SUMIFS(C2:C9,
A2:A9, ">="&EDATE(F1, SEQUENCE(12, 1, 0)), A2:A9, "<"&EDATE(F1, SEQUENCE(12, 1, 1))) =SUMIFS(C2:C9;
A2:A9; ">="&EDATE(F1; SEQUENCE(12; 1; 0)); A2:A9; "<"&EDATE(F1; SEQUENCE(12; 1; 1))) SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Revenue column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Date column, 8 cellsPress Escape to closecriteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".">=": comparisonsPress Escape to close&Joins textPress Escape to closeEDATE ZELFDE.DAGThe same day of the month, a number of months away.Read more about EDATEPress Escape to closestart_dateThe date to count from; its day number is kept where the month allows.Input cell: 1 Jan 2025Press Escape to closeSEQUENCE REEKSGenerates a list of numbers in steps.Used here as: monthsMonths to move: 12 is a year later, -3 is three months back.Read more about SEQUENCEPress Escape to closerowsHow many rows the block has.Press Escape to closecolumns (optional)How many columns; default 1.Press Escape to closestart (optional)The first number; default 1.Press Escape to closecriteria_range2 (optional)Range for a further condition; pairs repeat up to 127 times.Date column, 8 cellsPress Escape to closecriteria2 (optional)The test for criteria_range2, written like criteria1.Press Escape to close&Joins textPress Escape to closeEDATE ZELFDE.DAGThe same day of the month, a number of months away.Read more about EDATEPress Escape to closestart_dateThe date to count from; its day number is kept where the month allows.Input cell: 1 Jan 2025Press Escape to closeSEQUENCE REEKSGenerates a list of numbers in steps.Used here as: monthsMonths to move: 12 is a year later, -3 is three months back.Read more about SEQUENCEPress Escape to closerowsHow many rows the block has.Press Escape to closecolumns (optional)How many columns; default 1.Press Escape to closestart (optional)The first number; default 1.Press Escape to closeSUMIFS runs once per month: it adds the amounts dated on or after the first day of the month and before the first day of the next one. The result is a column of twelve totals, from 20,900 for January to 11,400 for December.
Step by step
- SEQUENCE(12, 1, 0) gives 0, 1, 2, up to 11. SEQUENCE(12, 1, 1) gives 1, 2, 3, up to 12.
- EDATE(F1, 0 to 11) turns the start date 1 January 2025 into the lower bounds: 1 Jan, 1 Feb, 1 Mar, up to 1 Dec 2025. EDATE(F1, 1 to 12) gives the upper bounds: 1 Feb 2025 up to 1 Jan 2026.
- The operators are joined to the dates, so SUMIFS gets twelve pairs of criteria: >=1 Jan and <1 Feb, then >=1 Feb and <1 Mar, and so on.
- January: 15 and 28 January qualify, so 12,50012.500 + 8,4008.400 = 20,90020.900. February: only 10 February, so 15,20015.200. March: no postings, so 0.
- April: 3 and 30 April, so 9,8009.800 + 6,3006.300 = 16,10016.100. May and June: 0. July: 21,00021.000. August, September and October: 0.
- November: 17,50017.500. December: the posting of 31 December is before 1 January 2026, so it counts: 11,40011.400. The twelve totals add up to 102,100102.100, the same as the eight postings: 12,50012.500 + 8,4008.400 + 15,20015.200 + 9,8009.800 + 6,3006.300 + 21,00021.000 + 17,50017.500 + 11,40011.400.
Why it works
SUMIFS accepts an array as criteria and then returns one result per element.
SEQUENCE supplies the month numbers, EDATE converts them to dates and handles different month lengths and the year change by itself, and the lower bound (included) and upper bound (excluded) cut the year into twelve slices without gaps or overlap. Change the start date in F1 and all twelve months follow.
Watch out for
- The start date must be the first day of a month. If
F1holds 15 January, every slice runs from the 15th to the 15th, and the totals no longer match the calendar months. - If the dates in column
Aare text (common after a CSV import), no posting meets the criteria and all twelve totals show 0 without a warning. Check thatA2is right-aligned or that ISNUMBER(A2) is TRUE, and compare the sum of the twelve months with the total of columnC. - Postings outside the twelve months are silently left out. Always compare the total of the twelve months with the total of the account, so a posting from 2024 or 2026 does not go unnoticed.
Variations
=SUMIFS(C2:C9,A2:A9, ">="&F1, A2:A9, "<"&EDATE(F1, 1))
=SUMIFS(C2:C9;A2:A9; ">="&F1; A2:A9; "<"&EDATE(F1; 1))
SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Read more about SUMIFSPress Escape to close
sum_rangeThe cells whose numbers are added when a row passes every test.Revenue column, 8 cellsPress Escape to close
criteria_range1The range the first condition is tested on; same size as sum_range.Date column, 8 cellsPress Escape to close
criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".">=": comparisonsPress Escape to close
&Joins textPress Escape to close
ReferenceInput cell: 1 Jan 2025Press Escape to close
criteria_range2 (optional)Range for a further condition; pairs repeat up to 127 times.Date column, 8 cellsPress Escape to close
criteria2 (optional)The test for criteria_range2, written like criteria1.Press Escape to close
&Joins textPress Escape to close
EDATE ZELFDE.DAGThe same day of the month, a number of months away.Read more about EDATEPress Escape to close
start_dateThe date to count from; its day number is kept where the month allows.Input cell: 1 Jan 2025Press Escape to close
monthsMonths to move: 12 is a year later, -3 is three months back.Positive = later. 0 = the same date. Negative = earlier. 12 = one year.Press Escape to close
The older approach for one month: the same two criteria without arrays, which you copy down twelve times with a moving start date. Gives 20,90020.900 for January.
=SUMIFS(C2:C9,A2:A9, ">="&F1, A2:A9, "<="&EOMONTH(F1, 0))
=SUMIFS(C2:C9;A2:A9; ">="&F1; A2:A9; "<="&EOMONTH(F1; 0))
SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Read more about SUMIFSPress Escape to close
sum_rangeThe cells whose numbers are added when a row passes every test.Revenue column, 8 cellsPress Escape to close
criteria_range1The range the first condition is tested on; same size as sum_range.Date column, 8 cellsPress Escape to close
criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".">=": comparisonsPress Escape to close
&Joins textPress Escape to close
ReferenceInput cell: 1 Jan 2025Press Escape to close
criteria_range2 (optional)Range for a further condition; pairs repeat up to 127 times.Date column, 8 cellsPress Escape to close
criteria2 (optional)The test for criteria_range2, written like criteria1.Press Escape to close
&Joins textPress Escape to close
EOMONTH LAATSTE.DAGThe last day of the month, a number of months away.Read more about EOMONTHPress Escape to close
start_dateThe date to start from; only its month and year matter.Input cell: 1 Jan 2025Press Escape to close
monthsMonths to move: 0 is this month, -1 last month, 1 next month.0: end of the same monthPress Escape to close
The upper bound as the last day of the month with EOMONTH. Gives 20,90020.900 for January too, but it misses postings that carry a time of day after midnight on the last day, so the first-day-of-next-month version is safer.
Related combinations
Functions used
- SUMIFSAdds the numbers in one range for the rows where every condition you set on other ranges is met.
- EDATEReturns the date that falls on the same day of the month a given number of months before or after a start date.
- SEQUENCEBuilds a block of consecutive numbers with a start value and a step, which spills into as many rows and columns as you ask for.
Functions used
- SUMIFSAdds the numbers in one range for the rows where every condition you set on other ranges is met.
- EDATEReturns the date that falls on the same day of the month a given number of months before or after a start date.
- SEQUENCEBuilds a block of consecutive numbers with a start value and a step, which spills into as many rows and columns as you ask for.