What would be best approach when I have 30k rows and I want several sums from it

[thepanu]

Can anyone give any recommendations as to what would be best approach when I have to calculate partial sums to hundreds of cells from dataset of tens of thousands of rows?

My data is pretty straightforward, one sheet, 8 columns and lots of rows. Two of the columns are used as index to help in my current method doing this which is using SUMPRODUCT, but it is slow.

Data is budgeting information containing info for location, budgeting month, budgeted month, account and value. And it is collected with macro from several sources (one for each location).

My report sheet is basically total sum for each account per budgeted month for given budgeting month. To clarify, sebtember is budgeting month for budgeted months Oct-Dec. So, months on columns and accounts on rows.

I looked into Database functions but it would seem that it is not possible to use them without criteria range and that means that I would need as many criteria ranges as I have cells with values in my report sheet.

So, is there any way to make this faster? Thanks in advance.

 

[SteveO59L]

Have a look at Pivot Tables

 

[thepanu]

Ah, that I forgot to mention. Pivot tables won't cut it as I need to keep old information aboard in report. So past months I need last figure and then most recent budget (this is set manually).

 

[MrKowz]

If you can't use pivot tables, then sumproduct might be the fastest way. If your overall spreadsheet is sluggish, consider turning automatic calculations off, and press F9 to calculate when you need to.

Are sumproducts the only formulas you are using? How many? There might be other formulas that are bogging it down.

However, any time you deal with that amount of data, spreadsheets can get slow.

 

[Fazza]

I think you will get a better answer if you provide more information. Ideally including some sample data to understand the setup.

IMO: generally SUMPRODUCT or similar array formulas will be slowest and best avoided at all costs (especially on large datasets); SUMIF in some instances is an excellent option (with helper field/s); pivot tables are good at the price of extra memory; query tables can be good (without the extra memory of the pivot table); custom functions or other approaches via VBA might be suitable. The objection about the pivot table perhaps can be worked around - I can't say without understanding more about the specific situation.

Maybe you don't even need to consolidate all the data from multiple sources to be able to generate the results?

Anyway, this is really only general. For better advice, please provide more information.

regards

 

[Ruddles]

You should also consider that Excel might not be the best tool to use. Do you have MS Access?