With help from this forum I have managed to fix some issues that, to me were very tricky, and have learned a lot of new techniques along the way.

But this latest one is again beyond me. Earlier I learnt some ways to do a sumifs equivalent across rows and columns in a single go. My aim is to build a fully dynamic model with both columns and rows sections dynamic. The issue I have now is similar to the previous one but with one added element.

I need to do a sumifs across columns too. I’ve managed to add multiple criteria in a single dimension ie the rows but to sum in 2 directions is just beyond me.

The background: I have a table of Trial Balances for multiple months. These need to go in on a monthly basis. But, the output sheet needs to have dynamic periods – ie the number of months in each period can change; typically  they will be 1,3,6 or 12 months in a period going from monthly to annual. These are driven elsewhere and the output sheet can have several ‘zones’ of different time periods. i.e. Early actuals may be annual, later ones may be 3 monthly or monthly, current year will be monthly and probably the early forecast years will be monthly and the later forecast years will be 3 monthly.  The actuals have to be in fixed time periods otherwise a change in configuration will result in them being in the wrong periods.

So I need to be able to do a sumif across the accounts by Entity (by rows) and also sum by columns based on the number of months in the periods. I have attached a sample sheet showing what I am trying to achieve. Ideally, I can avoid having and interim calculations sheet. ALSO, the inputs and outputs will be on different worksheets so I don’t think a solution with MMULT will work as I think it does not work across sheets.

Any help would be much appreciated.

​With help from this forum I have managed to fix some issues that, to me were very tricky, and have learned a lot of new techniques along the way.But this latest one is again beyond me. Earlier I learnt some ways to do a sumifs equivalent across rows and columns in a single go. My aim is to build a fully dynamic model with both columns and rows sections dynamic. The issue I have now is similar to the previous one but with one added element. I need to do a sumifs across columns too. I’ve managed to add multiple criteria in a single dimension ie the rows but to sum in 2 directions is just beyond me.The background: I have a table of Trial Balances for multiple months. These need to go in on a monthly basis. But, the output sheet needs to have dynamic periods – ie the number of months in each period can change; typically  they will be 1,3,6 or 12 months in a period going from monthly to annual. These are driven elsewhere and the output sheet can have several ‘zones’ of different time periods. i.e. Early actuals may be annual, later ones may be 3 monthly or monthly, current year will be monthly and probably the early forecast years will be monthly and the later forecast years will be 3 monthly.  The actuals have to be in fixed time periods otherwise a change in configuration will result in them being in the wrong periods. So I need to be able to do a sumif across the accounts by Entity (by rows) and also sum by columns based on the number of months in the periods. I have attached a sample sheet showing what I am trying to achieve. Ideally, I can avoid having and interim calculations sheet. ALSO, the inputs and outputs will be on different worksheets so I don’t think a solution with MMULT will work as I think it does not work across sheets. Any help would be much appreciated.  Read More

​