Spreadsheet: 756135d1637301836-using-sumif-with-dynamic-arrays-to-sum-column-totals-horizontal-totalization-dj
Sheet: Calculations

 | Calculations
 | Model Timeline
 | Period Number |  |  |  |  |  |  |  | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20
 | Month Start Date |  |  |  |  |  |  |  | 2020-08-01 00:00:00 | 2020-09-01 00:00:00 | 2020-10-01 00:00:00 | 2020-11-01 00:00:00 | 2020-12-01 00:00:00 | 2021-01-01 00:00:00 | 2021-02-01 00:00:00 | 2021-03-01 00:00:00 | 2021-04-01 00:00:00 | 2021-05-01 00:00:00 | 2021-06-01 00:00:00 | 2021-07-01 00:00:00 | 2021-08-01 00:00:00 | 2021-09-01 00:00:00 | 2021-10-01 00:00:00 | 2021-11-01 00:00:00 | 2021-12-01 00:00:00 | 2022-01-01 00:00:00 | 2022-02-01 00:00:00
 | Month End Date |  |  |  |  |  |  |  | 2020-08-31 00:00:00 | 2020-09-30 00:00:00 | 2020-10-31 00:00:00 | 2020-11-30 00:00:00 | 2020-12-31 00:00:00 | 2021-01-31 00:00:00 | 2021-02-28 00:00:00 | 2021-03-31 00:00:00 | 2021-04-30 00:00:00 | 2021-05-31 00:00:00 | 2021-06-30 00:00:00 | 2021-07-31 00:00:00 | 2021-08-31 00:00:00 | 2021-09-30 00:00:00 | 2021-10-31 00:00:00 | 2021-11-30 00:00:00 | 2021-12-31 00:00:00 | 2022-01-31 00:00:00 | 2022-02-28 00:00:00
 | Calendar Year |  |  |  |  |  |  |  | 2020 | 2020 | 2020 | 2020 | 2020 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2022 | 2022
 | Calendar Month |  |  |  |  |  |  |  | 8 | 9 | 10 | 11 | 12 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 1 | 2
 | Financial Year |  |  |  |  |  |  |  | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022
 | Model Year |  |  |  |  |  |  |  | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
 | Multiply by 1 |  |  |  |  |  |  |  | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
 | Days in Period |  |  |  |  |  |  |  | 31 | 30 | 31 | 30 | 31 | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 | 31 | 28
 | Business Days in Period (ex Public Holidays) |  |  |  |  |  |  |  | 21 | 22 | 22 | 21 | 23 | 21 | 20 | 23 | 22 | 21 | 22 | 22 | 22 | 22 | 21 | 22 | 23 | 21 | 20
 | Month + Year |  |  |  |  |  |  |  | 2020-08-31 00:00:00 | 2020-09-30 00:00:00 | 2020-10-31 00:00:00 | 2020-11-30 00:00:00 | 2020-12-31 00:00:00 | 2021-01-31 00:00:00 | 2021-02-28 00:00:00 | 2021-03-31 00:00:00 | 2021-04-30 00:00:00 | 2021-05-31 00:00:00 | 2021-06-30 00:00:00 | 2021-07-31 00:00:00 | 2021-08-31 00:00:00 | 2021-09-30 00:00:00 | 2021-10-31 00:00:00 | 2021-11-30 00:00:00 | 2021-12-31 00:00:00 | 2022-01-31 00:00:00 | 2022-02-28 00:00:00
 | Actual/Forecast |  |  |  |  |  |  |  | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast
 | Profit and Loss
 |  |  |  |  | CF Flag
 | P&L
 | Other revenue |  |  |  | A |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 32000 | 33000 | 34000 | 35000 | 36000 | 37000 | 38000 | 39000
 | Total revenue |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 32000 | 33000 | 34000 | 35000 | 36000 | 37000 | 38000 | 39000
 | Other direct costs |  |  |  | A |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 | Total direct costs |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 | Gross margin |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 32000 | 33000 | 34000 | 35000 | 36000 | 37000 | 38000 | 39000
 | GM %
 | Bank fees and borrowing costs |  |  |  | A |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -10 | -10 | -10 | -10 | -10 | -10 | -10 | -10
 | Consultancy costs |  |  |  | B |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -20000 | -20000 | -20000 | -20000 | -20000 | -20000 | -20000 | -20000
 | Other overhead costs |  |  |  | C |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -450 | -450 | -450 | -450 | -450 | -450 | -450 | -450
 | Total overheads |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -20460 | -20460 | -20460 | -20460 | -20460 | -20460 | -20460 | -20460
 | Other income |  |  |  | A |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 | Total other items |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 | EBITDA |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11540 | 12540 | 13540 | 14540 | 15540 | 16540 | 17540 | 18540
 | EBITDA % |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.360625 | 0.38 | 0.3982352941176471 | 0.4154285714285714 | 0.4316666666666666 | 0.447027027027027 | 0.461578947368421 | 0.4753846153846154
 | Depreciation and amortisation |  |  |  | A
 | Gain / (loss) on sale of assets |  |  |  | A
 | Total D&A
 | EBIT |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11540 | 12540 | 13540 | 14540 | 15540 | 16540 | 17540 | 18540
 | EBIT % |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.360625 | 0.38 | 0.3982352941176471 | 0.4154285714285714 | 0.4316666666666666 | 0.447027027027027 | 0.461578947368421 | 0.4753846153846154
 | A Cashflows |  |  |  | A |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 31990 | 32990 | 33990 | 34990 | 35990 | 36990 | 37990 | 38990
 | B Cashflows |  |  |  | B |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -20000 | -20000 | -20000 | -20000 | -20000 | -20000 | -20000 | -20000
 | C Cashflows |  |  |  | C |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -450 | -450 | -450 | -450 | -450 | -450 | -450 | -450
 |  |  |  |  |  |  |  | =TRANSPOSE(MMULT(TRANSPOSE(SumRange*($F$25:$F$46=$F52))*ROW($A$1:$A$20)^0,ROW($I$25:$I$46)^0))