Please provide an array formula. Can you help to reset running totals when MOQ is reached?. Here MOQ=15. When running total becomes equal to or greater than 15 it should restart.
| Date | Value | Desired |
|---|---|---|
| 12/2022 | 6 | 6 |
| 01/2023 | 5 | 11 |
| 02/2023 | 4 | 15 |
| 03/2023 | 3 | 3 |
| 04/2023 | 9 | 12 |
| 05/2023 | 2 | 14 |
| 06/2023 | 6 | 20 |
| 07/2023 | 1 | 1 |
| 08/2023 | 6 | 7 |
| 09/2023 | 1 | 8 |
| 10/2023 | 8 | 16 |
| 11/2023 | 9 | 9 |
| 12/2023 | 3 | 12 |
all we need is a unique common ID for grouping the sum. we start with months to fall from whatever date to months 3, 7 and 11:
=ARRAYFORMULA(IFNA(VLOOKUP(MONTH(A2:A), {3;7;11}, 1, 1), 11))
next, we can use years to differentiate between 11/2022 and 11/2023 so we take whatever date and convert it into first day of given month and then offset the year by 58 days:
=ARRAYFORMULA(YEAR(EOMONTH(A2:A, -1)+1-58))
we combine it to get a unique ID per MOQ:
=ARRAYFORMULA(IFERROR(IFNA(VLOOKUP(MONTH(A2:A), {3;7;11}, 1, 1), 11)&
" "&YEAR(EOMONTH(A2:A, -1)+1-58)))
then we just use standard running total fx:
=ARRAYFORMULA(IF(A2:A="",,
MMULT(--TRANSPOSE(IF((TRANSPOSE(ROW(A2:A))>=ROW(A2:A))*(
IFERROR(IFNA(VLOOKUP(MONTH(A2:A), {3;7;11}, 1, 1), 11)&"×"&
YEAR(EOMONTH(A2:A, -1)+1-58))=TRANSPOSE(
IFERROR(IFNA(VLOOKUP(MONTH(A2:A), {3;7;11}, 1, 1), 11)&"×"&
YEAR(EOMONTH(A2:A, -1)+1-58)))), B2:B, 0)), ROW(A2:A)^0)))
=ARRAYFORMULA(IF(A2:A="",, MMULT(--TRANSPOSE(IF((TRANSPOSE(ROW(B2:B))>=ROW(B2:B))*(
ARRAY_CONSTRAIN({0; IF(TRUNC(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B)/15)>
MAX(TRUNC(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B)/15))-1,
MAX(TRUNC(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B)/15))-1,
TRUNC(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B)/15))}, ROWS(B2:B), 1)=TRANSPOSE(
ARRAY_CONSTRAIN({0; IF(TRUNC(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B)/15)>
MAX(TRUNC(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B)/15))-1,
MAX(TRUNC(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B)/15))-1,
TRUNC(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B)/15))}, ROWS(B2:B), 1))), B2:B, 0)),
ROW(B2:B)^0)))
