SEQUENCE

At Veldhuis Installatie BV you have picked five customers from the receivables list for balance confirmations. Your workpaper needs a reference number per line, so letters and replies can be tied back to the list. You want the numbers to come from a formula, not from typing.

Formula in D2

=SEQUENCE(5)

=SEQUENCE(5)

Result: 1 / 2 / 3 / 4 / 51 / 2 / 3 / 4 / 5

Watch out: If anything is typed in D3:D6, the formula shows #SPILL! instead of the numbers. Keep the cells below the formula empty.

=SEQUENCE(ROWS(A2:A6))

=SEQUENCE(ROWS(A2:A6))

=SEQUENCE(1, 5)

=SEQUENCE(1; 5)

You test eight purchase invoices of Veldhuis Installatie BV with a systematic sample: a fixed interval after a first position. You type the first position and the interval in F1 and F2, and want the three positions to appear, so you can pick those lines from the list. The positions must stay within the 8 lines of the list.

Formula in E3

=SEQUENCE(3, 1, F1, F2)

=SEQUENCE(3; 1; F1; F2)

Result: 2 / 5 / 82 / 5 / 8

Watch out: Nothing checks that the last position fits the list. Start 2, step 3 and 4 items would end at 11 in a list of 8 lines. Check that start + (n-1) × step is at most the number of lines.

=INDEX(A2:A9, SEQUENCE(3, 1, F1, F2))

=INDEX(A2:A9; SEQUENCE(3; 1; F1; F2))

=SEQUENCE(3, 1, 8, -3)

=SEQUENCE(3; 1; 8; -3)

For the revenue analysis of Molenaar Bouw BV you have the postings on account 8000 for the first months of 2025. You want one total per month for January to April, without a helper column and without typing month boundaries.

The first day of the period sits in F1. A posting from May is in the list too, and should not be counted.

Formula in E3

=SUMIFS(B2:B9, A2:A9, ">="&EDATE(F1, SEQUENCE(4, 1, 0)), A2:A9, "<"&EDATE(F1, SEQUENCE(4, 1, 1)))

=SUMIFS(B2:B9; A2:A9; ">="&EDATE(F1; SEQUENCE(4; 1; 0)); A2:A9; "<"&EDATE(F1; SEQUENCE(4; 1; 1)))

Result: €20,500.00 / €25,000.00 / €17,400.00 / €7,300.00€ 20.500,00 / € 25.000,00 / € 17.400,00 / € 7.300,00

Watch out: The count 4 is typed twice. If you change one SEQUENCE to 5 and forget the other, the two criteria arrays have different sizes and the result is wrong or an error. Take the count from one cell instead.

=EDATE(F1, SEQUENCE(4, 1, 0))

=EDATE(F1; SEQUENCE(4; 1; 0))

=SUMIFS(B2:B9, A2:A9, ">="&F1, A2:A9, "<"&EDATE(F1, 4))

=SUMIFS(B2:B9; A2:A9; ">="&F1; A2:A9; "<"&EDATE(F1; 4))

Hendriks Metaal BV has a broken fiscal year from 1 July 2025 to 30 June 2026. For your analytical review you want purchases per month in a grid of four quarters (rows) by three months (columns). The grid must name its steps for the reviewer and must refuse to show numbers that do not add up to the ledger, for example when a posting falls outside the fiscal year.

Formula in E3

=LET(
  fyStart, F1,
  offsets, SEQUENCE(4, 3, 0),
  monthStart, EDATE(fyStart, offsets),
  nextMonth, EDATE(fyStart, offsets + 1),
  totals, SUMIFS(C2:C9, B2:B9, ">="&monthStart, B2:B9, "<"&nextMonth),
  IF(SUM(totals) = SUM(C2:C9), totals, "Postings outside the fiscal year")
)

=LET(
  fyStart; F1;
  offsets; SEQUENCE(4; 3; 0);
  monthStart; EDATE(fyStart; offsets);
  nextMonth; EDATE(fyStart; offsets + 1);
  totals; SUMIFS(C2:C9; B2:B9; ">="&monthStart; B2:B9; "<"&nextMonth);
  IF(SUM(totals) = SUM(C2:C9); totals; "Postings outside the fiscal year")
)

Result: €4,200.00 | €8,150.00 | €0.00 / €9,100.00 | €0.00 | €2,750.00 / €5,600.00 | €0.00 | €0.00 / €3,900.00 | €0.00 | €8,250.00€ 4.200,00 | € 8.150,00 | € 0,00 / € 9.100,00 | € 0,00 | € 2.750,00 / € 5.600,00 | € 0,00 | € 0,00 / € 3.900,00 | € 0,00 | € 8.250,00

Watch out: The mirror-image mistake is SEQUENCE(3, 4, 0). It still covers twelve months and the reconciliation still passes, but the grid now has 3 rows of 4 months, so rows are no longer quarters and a reader of the headers is misled. Rows come first: 4 quarters, then 3 months.

=LET(
  months, EDATE(F1, SEQUENCE(4, 3, 0)),
  SUMIFS(C2:C9, B2:B9, ">="&months, B2:B9, "<"&EDATE(months, 1))
)

=LET(
  months; EDATE(F1; SEQUENCE(4; 3; 0));
  SUMIFS(C2:C9; B2:B9; ">="&months; B2:B9; "<"&EDATE(months; 1))
)