01 / 12The Covenant Passed on a Number With Two Decembers

ReportingSUMIFSEOMONTH

Rolling 12-Month Totals in Excel With SUMIFS and EOMONTH

Four Covenant Tests Passed on a Rolling Twelve Months That Held Thirteen, Because the Sheet Summed Twelve Rows and the Month the Kitchen Shut Had No Row

28 Oct 202610 min read

UsesSUMIFSEOMONTHCOUNTIFSSUM

Copperfield Kitchens' loan needed rolling twelve-month revenue of at least £1,500,000 at every quarter end. In 2025 the sheet reported four passes. Every one of them was a breach, and the bank found out first.

The sheet added up the last twelve rows of a column of monthly totals. That is twelve months until a month goes missing, and in February 2025 one did.

Covenant test, 2025Figure
Floor, rolling 12 months£1,500,000
December, as the sheet read it£1,653,000
December, real twelve months£1,470,600
Counted twice: December 2024£182,400
Tests passed on the sheet4 of 4
Tests passed in fact0 of 4

Scope. SUMIFS, COUNTIFS, AVERAGEIFS, EOMONTH and EDATE work the same in every current version of Excel on Windows, Mac and the web, and in Google Sheets. SEQUENCE in section 6 needs Excel 2021 or Microsoft 365; a fill-down version is given beside it.

Throughout, A2:A24 holds month-end dates and B2:B24 that month's revenue. A rolling twelve-month total for a month means that month and the eleven calendar months before it.

01The Covenant Passed on a Number With Two Decembers

The kitchen shut for a refit in February 2025 and raised no invoices that month. The accounts export lists only months with postings, so the monthly table went straight from January 2025 to March 2025.

Nobody noticed, because the rolling column kept producing numbers. From March onwards each one was twelve rows deep and thirteen months wide.

In October 2026 the bank's annual review rebuilt the tests from the monthly management accounts. Its December 2025 figure was £1,470,600. The sheet's was £1,653,000, and the difference was exactly December 2024's revenue.

Twenty-Three Monthly Totals, One Missing Month and Two Rolling Totals

Copperfield Kitchens' monthly revenue from the accounts export, in A1:F24. Column A holds month-end dates shown as mmm yyyy; there is no February 2025, because the kitchen was shut for a refit and the export lists only months with postings. Column C is the sheet's `=SUM(B2:B13)` filled down, column D the calendar months those twelve rows span, column E the twelve-month window by date with `SUMIFS` and `EOMONTH`, and column F the months that window actually found.

ABCDEF
1
Month
Revenue
12-row total
Months spanned
Date-window total
Months in window
2
Jan 2024
98400
3
Feb 2024
104200
4
Mar 2024
112600
5
Apr 2024
118900
6
May 2024
126300
7
Jun 2024
131800
8
Jul 2024
124500
9
Aug 2024
109700
10
Sep 2024
127200
11
Oct 2024
133600
12
Nov 2024
148900
13
Dec 2024
182400
1518500
12
1518500
12
14
Jan 2025
101300
1521400
12
1521400
12
15
Mar 2025
116800
1534000
13
1421400
11
16
Apr 2025
123400
1544800
13
1425900
11
17
May 2025
131900
1557800
13
1431500
11
18
Jun 2025
138200
1569700
13
1437900
11
19
Jul 2025
129600
1567500
13
1443000
11
20
Aug 2025
113800
1556800
13
1447100
11
21
Sep 2025
132500
1579600
13
1452400
11
22
Oct 2025
139100
1591500
13
1457900
11
23
Nov 2025
154700
1612600
13
1463700
11
24
Dec 2025
189300
1653000
13
1470600
11

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

02Twelve Rows Are Not Twelve Months

The sheet's formula in C13 was =SUM(B2:B13), filled down to C24. Each copy adds the twelve rows ending on its own row. It counts rows, and it trusts every row to be a month.

For December 2024 and January 2025 that holds, and the totals are right. From March 2025 the twelve rows start a month earlier than they should, because one of the months they are meant to cover has no row.

Test12-row totalMonths spannedReal 12 monthsVerdict
Mar 20251,534,000131,421,400Breach
Jun 20251,569,700131,437,900Breach
Sep 20251,579,600131,452,400Breach
Dec 20251,653,000131,470,600Breach

Every wrong total is too high by the revenue of the month thirteen back. In December that is the biggest month of the year, which is why the error was largest exactly where the annual figure is read.

Scenario: Enter the monthly table with no February 2025 row, put =SUM(B2:B13) in C13 and fill it to C24. Then check C24 against =SUM(B14:B24), the eleven rows that really fall in 2025.

Try it in the grid

03Define the Window by Date, Not by Position

A window defined by dates cannot be stretched by a missing row. Ask for every row whose month falls after the month-end a year back, and on or before this month-end.

=SUMIFS($B$2:$B$24,$A$2:$A$24,">"&EOMONTH(A13,-12),$A$2:$A$24,"<="&A13)

EOMONTH(A13,-12) returns the last day of the month twelve months before A13. For December 2025 that is 31 December 2024, and the > leaves it out. The window is January to December 2025, whatever rows exist inside it.

Filled down column E, it returns £1,470,600 for December 2025. On a sheet with whole columns free of other data, the short form reads the same way: =SUMIFS(B:B,A:A,">"&EOMONTH(A24,-12),A:A,"<="&A24).

Scenario: Put the SUMIFS formula above in E13 and fill it to E24. Confirm E13 and E14 match column C, then confirm E24 reads 1,470,600 where C24 reads 1,653,000.

Try it in the grid

04The Off-by-One That Makes It Thirteen Again

The date window has one fragile character. With month-end dates, >= instead of > adds a thirteenth month, because the month-end a year back is itself a row in the table.

Window forWith >With >=Months with >=
Jan 20251,521,4001,619,80013
Mar 20251,421,4001,534,00012 of 13
Dec 20251,470,6001,653,00012 of 13

From March 2025 the >= version reproduces the 12-row column to the pound. Two wrong methods agreeing with each other is not a check. In January 2025 it is wrong even where the row count was right.

If your table holds first-of-month dates instead, the boundaries move: ">="&EDATE(A13,-11) and "<="&A13. Pick one date convention for the column and write the window for that convention.

Scenario: Copy column E to column G and change the first ">" to ">=". Compare G14 with E14 for January 2025 and find the 98,400 of January 2024 that the extra character pulled in.

Try it in the grid

05Count the Months in Every Window

A total cannot tell you how many months it contains. A count can. Put the same two criteria into COUNTIFS beside every rolling total.

=COUNTIFS($A$2:$A$24,">"&EOMONTH(A13,-12),$A$2:$A$24,"<="&A13)

It returns 12 for December 2024 and January 2025, then 11 from March 2025 onwards. That 11 is the missing February, visible on every row the 12-row column got wrong.

The 12-row column needs a check of its own: the calendar months its rows span. =(YEAR(A13)-YEAR(A2))*12+MONTH(A13)-MONTH(A2)+1, filled down, returns 12 and then 13. Either check would have stopped the March certificate.

Scenario: Add the COUNTIFS column beside your rolling totals, then add =COUNTIF(F13:F24,"<>12") above it. Copperfield's table returns 10, one for every month-end since the gap.

Try it in the grid

06A Closed Month Is a Zero, Not a Gap

The covenant is right to count February 2025 as a month with no revenue. The table was wrong to leave it out. Build the months first, then fill in what each one earned.

=EOMONTH(DATE(2024,1,1),SEQUENCE(24,1,0))

That spills 24 month-ends from January 2024 to December 2025. Without SEQUENCE, type 31/01/2024 in A2 and fill =EOMONTH(A2,1) down from A3. Then fetch each month's revenue from the export with SUMIFS, which returns 0 for a month with no postings.

With a row for every month, the 12-row sum is right again and the date window still agrees with it. Keep the date window anyway. The next gap may come from a filter, a deleted row or a second export.

Scenario: Build the month spine in a new column A, then enter =SUMIFS(Export!B:B,Export!A:A,A2) in B2 and fill down. Check that the February 2025 row reads 0 rather than being absent.

Try it in the grid

07Leave the First Eleven Months Blank

Filled to the top of the table, the date window returns a total for January 2024 of £98,400. It is one month of revenue labelled as twelve. A partial window should be blank, not small.

=IF(EOMONTH(A2,-12)<EOMONTH(MIN($A$2:$A$24),-1),"",
    SUMIFS($B$2:$B$24,$A$2:$A$24,">"&EOMONTH(A2,-12),$A$2:$A$24,"<="&A2))

The test asks whether the window starts before the data does. For November 2024 it does, and the cell stays empty. From December 2024, the first month with a full year behind it, the total appears.

This is not the same as a missing month in the middle. The start of the data is known and expected; a gap inside it is a fault, and the month count in section 5 is what reports it.

08Year on Year Means Two Windows of Twelve

The board pack said rolling revenue was up 8.9% on the year. It compared C24, thirteen months, with C13, twelve. The real movement was down 3.2%.

December windowSheetReal
20241,518,5001,518,500
20251,653,0001,470,600
Change+134,500−47,900
Change, %+8.9%−3.2%

The comparison is =E24/E13-1, and it is only fair when both cells hold twelve months. Show the two month counts beside it, so a reader can see they match before trusting the percentage.

Scenario: Enter =E24/E13-1 and =C24/C13-1 side by side, formatted as percentages. Then put =F24=F13 beneath them and read why one of the two answers is fair.

Try it in the grid

09A Rolling Average Divides by Twelve

A rolling monthly average is the rolling total divided by twelve. AVERAGEIFS over the same window divides by the rows it finds instead, which after the gap is eleven.

December 2025FormulaResult
Rolling total ÷ 12=E24/12122,550.00
AVERAGEIFS over the windowrows found: 11133,690.91

The covenant treats February as a month that earned nothing, so £122,550 is the honest average. AVERAGEIFS silently describes a business that was open every month and earned over £11,000 more in each.

Neither is wrong as arithmetic. Decide whether a closed month counts, write it down beside the figure, and use the formula that matches.

10Six Traps

  1. Twelve rows for twelve months. Any missing month stretches the window, and nothing in the total shows it.
  2. >= on month-end dates. It includes the month-end a year back and makes the window thirteen months long.
  3. Checking one method against another. The 12-row sum and the >= window agree from March 2025, and both are wrong.
  4. Partial windows at the start. A January "rolling total" of one month looks like a collapse, not like missing history.
  5. Year on year across uneven windows. Thirteen months against twelve is growth that never happened.
  6. AVERAGEIFS for a rolling average. It divides by the rows it finds, so a closed month disappears from the denominator.

11Four Exercises

  1. Reproduce it. Enter the 23 monthly rows with no February 2025, fill =SUM(B2:B13) from C13 to C24 and find the first wrong row.
  2. Fix the window. Replace column C with the SUMIFS and EOMONTH window from section 3, and check that December 2025 reads 1,470,600.
  3. Break it on purpose. Change > to >= and find the 98,400 it adds to January 2025.
  4. Prove it. Add the COUNTIFS month count, then insert a February 2025 row with 0 revenue and watch every count return to 12.

What to Take Away

Copperfield's sheet was right for two months and wrong for ten, and the wrong ones were the covenant tests. A spot check of December 2024 would have passed, which is how four certificates went to the bank.

A month is a date, not a position. Define the window with dates, count the months inside it, and keep a row for every month, including the ones that earned nothing.

The bank now has a breach to discuss for every quarter of 2025. The sheet gave Copperfield four quarters of notice and showed none of it.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge