MAXIFS

For the purchases cycle at Veldhuis Installatie BV you have the bank payments to three suppliers in the last quarter of 2025. You want the largest single payment to the supplier named in F1, to compare with the approval limit for payments. MAX without a condition would return the largest payment of all suppliers together.

Formula in F2

=MAXIFS(C2:C7, A2:A7, F1)

=MAXIFS(C2:C7; A2:A7; F1)

Result: €5,640.00€ 5.640,00

Watch out: Type a supplier name that is not in the list, or one with a space at the end, and the result is 0, not an error. A real payment of 0.00 would look the same, so check the name whenever you see 0.

=MAX(C2:C7)

=MAX(C2:C7)

For the cut-off test on sales you want, for one customer, the date of the most recent sales invoice on or before the balance sheet date of 31 December 2025.

The list also holds invoices from January 2026. They belong to the next year and must not count. The formula sits in F4 and is meant to be copied down for more customers, so the ranges are locked.

Formula in F4

=MAXIFS($C$2:$C$8, $B$2:$B$8, E4, $C$2:$C$8, "<="&$F$1)

=MAXIFS($C$2:$C$8; $B$2:$B$8; E4; $C$2:$C$8; "<="&$F$1)

Result: 22 Dec 202522 Dec 2025

Watch out: Write "<=F1" without the & and Excel compares with the text F1. No date passes and the result is 0, which a date format shows as 0 January 1900.

=MAXIFS($C$2:$C$8, $B$2:$B$8, E4)

=MAXIFS($C$2:$C$8; $B$2:$B$8; E4)

In the purchases cycle every invoice of 10,000.0010.000,00 or more needs approval from the directors. You test whether the supplier in F1 stays close to that limit on purpose, which can point to split invoices.

You want the largest invoice below the limit in F2, and a flag when it lies within the margin in F3 (5%) of the limit.

Formula in F4

=IF(MAXIFS(C2:C9, A2:A9, F1, C2:C9, "<"&F2) >= F2 * (1 - F3), "Near the limit", "Clear of the limit")

=IF(MAXIFS(C2:C9; A2:A9; F1; C2:C9; "<"&F2) >= F2 * (1 - F3); "Near the limit"; "Clear of the limit")

Result: Near the limit

Watch out: A supplier with no invoice below the limit, or a name with a typo, gives 0 from MAXIFS. Since 0 is below 9,500.009.500,00 the flag reads Clear of the limit without any error, so a clear result is only as reliable as the supplier name in F1.

=MAXIFS(C2:C9, A2:A9, F1, C2:C9, "<"&F2)

=MAXIFS(C2:C9; A2:A9; F1; C2:C9; "<"&F2)

=COUNTIFS(A2:A9, F1, C2:C9, ">="&F2 * (1 - F3), C2:C9, "<"&F2)

=COUNTIFS(A2:A9; F1; C2:C9; ">="&F2 * (1 - F3); C2:C9; "<"&F2)

For a test on inactive suppliers at 31 December 2025 you want, for each supplier in the master data, the number of days since the last payment of at least 1,000.001.000,00 on or before the report date.

A supplier without such a payment must read 'No payment' and not 'days since 30 December 1899'. Payments after the report date are subsequent events and must not count.

Formula in F2

=LET(
  suppliers, E2:E5,
  cutoff, H1,
  minAmt, H2,
  last, MAXIFS(B2:B9, A2:A9, suppliers, B2:B9, "<="&cutoff, C2:C9, ">="&minAmt),
  IF(last = 0, "No payment", cutoff - last)
)

=LET(
  suppliers; E2:E5;
  cutoff; H1;
  minAmt; H2;
  last; MAXIFS(B2:B9; A2:A9; suppliers; B2:B9; "<="&cutoff; C2:C9; ">="&minAmt);
  IF(last = 0; "No payment"; cutoff - last)
)

Result: 26, 47, 149 and No payment26. 47. 149 and No payment

Watch out: The mirror image looks right: write ">="&cutoff instead of "<="&cutoff and the formula sees only payments after the report date. With these rows it returns -9 for Bakker (its payment of 9 January 2026) and No payment for the other three suppliers. On a long list this can pass for an exception report, so always test with a payment dated after the report date.

=MAXIFS(B2:B9, A2:A9, E2, B2:B9, "<="&H1, C2:C9, ">="&H2)

=MAXIFS(B2:B9; A2:A9; E2; B2:B9; "<="&H1; C2:C9; ">="&H2)