Too many SUMIFs cripples computer?

I have 15,000 rows of data that I am checking to populate three tabs one 600 rows, one 137 rows and the last 12. But every dealer has a row and every model has a column and the 15,000 data entries populate them all.

I concatenated the dealer #, Model, & Program # to create unique variables for SUMIF to populate everything and it does, but I have to shut everything else down and it runs very slowly. I watch the Calculating % creep up... It was put together in a rush. THere are no links and CTRL END reveals no excess rows or columns. Is this just the reality of SUMIF or is there something else to look for? Any other recommendations to populate a cartesian plain from a data dump out of SAP?

Any suggestions are welcome, Sales MGR complaining his computer is crippled too, I plan to copy and paste after latest data dump but don't want to have to keep doing that if possible...

Cheers
Grasstoe

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

I think I have rendered this into a small enough sample, I have the full version open at the same time and it is painful to wait for it to finish saving. The Summary tab shows all the order taken for each dealer populated from the Data tab... on the fully loaded version every column is doing this, there are a lot more dealers and models, but this should give one the gist...

Thank You so much!!
Grasstoe

There are more tabs that further summarize the three lists of dealers, but a variety of pivot tables could generate a decent answer. There are aspects though were a dealers recommended order is only counted when they submit their actual order using {formulas} but it is definitely worth exploring...

I've no idea what your last post means, but this seems to have the same answers

Only that what I attached is the part of a more complicated workbook that contains the issue I believe is slowing me down...

Sorry the {formula} was meant to be an Array Formula and the name was eluding me. I have a recommended order for all dealers but I care about our achievement during the order period based on how we are doing with orders against only their recommended orders rather than against all dealers recommended orders. As such I have cells that only populate when a dealer submits an order, and on my report tab that in turn triggers my array formula to include the recommended order for that dealer or not... I inherited this and normally we populate it manually this time we are trying to populate it from data in our system as dealers order online instead of on paper... I did it in a rush and I wish I had a better answer, But perhaps my Dealer tabs for each row could be answered by pivot tables. SOmetime I have difficulty accessing data from a pivot table out to a seperate summary, but I will have to play with it.

I am not usually a fan of pivot tables either but I am drooling over how well this works now! GetPivotData worked like a charm!
Thank You!
Grasstoe

Originally Posted by MrShorty

700+ sumifs of unknown # of operations per sumifs over a database of 15000 records could easily run into a lot of operations and take a while to calculate.

Assuming that your source data is in a good database format, can you run three pivot tables off of the source data instead? Pivot tables are (almost) always faster than worksheet functions. https://www.excel-easy.com/data-****...ot-tables.html

The data exceeded 15,000 last time through so I found and replaced all the 15,000s with 20,000 and there were over 150,000 updates so yes Sumif worked but it was terribly inefficient. Pivot Tables and GetPivotData got me so excited I had to drag someone into my office and show them how well it worked, with no functionality delay!!
Thank YOU

Originally Posted by FDibbins

Another option might be to use helper column/s to do some of the heavy lifting.
And if you are indeed using ARRAY formulas, as you suggest, dont use full-column references, rather use just the range you need - or if that can grow, then 2-3 times what you think you will need.

I am not sure what a helper Column is? I ended up creating pivot tables and using getpivotdata to arrange the data the way I needed...the array formulas are very specific so I don't think they are the delay but good to keep in mind...

Thank you
grasstoe

Originally Posted by Zer0Cool

Also after running Inquire | Workbook Analysis I see you have alot of formulas like:

Formula:

Please Login or Register  to view this content.

This could be done instead simply using number formats. You could do it in the C column and eliminate the need for this formula and column entirely or keep the column, simply reference the C columns cell ex: =C3727 and then just format the cell the reference is in as "000000", which would ensure any entry was at least 6 digits with leading 0's. IE: 1 = 000001, 12 = 000012, 123 = 000123, 1234 = 001234, etc until 123456 = 123456.

Alternatively, if you need the leading 0's in the cell, use =TEXT(C3727, "000000") instead, again much more efficient.

This would have a much lower impact on calc times than your formula does.

You have 29,000 formulas in your workbook and the vast majority of them are this IF(CONCATE(LEN mess.

Also, back to the matter of excess (formats and cells/content) clearing both made your sample go from 923k to 584k in file size with no other change on my part. So the file is 36.7% smaller in file size now just by clearing the excess out and leaving all the same data as in your sample. Thats a huge difference and would certainly help with load times and likely calc times too.

EDIT: replacing all the IF(CONCAT(LEN formulas with =TEXT reduced the file size another ~20ish kb (due to simplifying the calc chain) and reduced the calc chain in its uncompressed state around 100kb, so file size of ~566k.

Imagine my hand slapping my forehead! I deal with the digit issue a lot so fixing using formating sounds great. Using the Pivot table idea I can delete all those unnecessary references... Thanks for posting and looking into my mess, I will definitely use that idea in the near future.

Thank YOU!

Why don't you like Pivot Tables?

Originally Posted by Kyle123

Why don't you like Pivot Tables?

Trying to learn from all the posts I deleted Columns A & B from my Data but then discovered that my 16,000 rows were using a dealer # formated as text. So I needed another column to pad the leading zeroes. I was able to update the pivot tables very quickly but I had to redo all the GetPivotTable references even though the data was in the same location... More than anything I use them so rarely even though they are regularly very helpful that I always feel like I am starting over. I need to just immerse myself and get over it...