Summing across months

[cosmokramer]

Hello,

I have a spreadsheet with work whereby they want predicted sales figures for each month.

The way the spreadsheet is setup at the moment is that we have tabs for each business unit with a list of items each having a column for date and sales figure.

What I want to do is have a seperate tab that sums all the sales figures from every tab by month.

The way I do it at the moment is filter each tab against the date (i.e. all of May) and then drag the cursor down the sales figures to get a sum in the bottom right of the screen. Then manually enter this figure into the table on the summary tab. I do this for all business unit tabs. It is very labour intensive and also is not accurate as it does not capture any changes people make to the figures in the business unit tabs. So at the end of the month i have to go through the whole process again.

Is there a formulae that would provide a solution for this, or are there any tips on how to improve the process?

Would really appreciate any assistance you guys could offer!

Thanks!

 

[SpillerBD]

It depends on the version of Excel you have.
With 2016, I would use PowerQuery to provide a consolidated DataModel to build a PivotTable from.
If you wish to keep it as a formula, you would best be served by using Tables which simplifies your multiple segments to make your complete formula.

If the list of Products changes, the PivotTable will be more adaptive.

 

[cosmokramer]

Hi, thanks for that - I am now looking into a turotial for the PowerQuery PivtoTable solution you have mentioned.

It is 2016 that I am using.