SUMIFS formula to replace SUMPRODUCT because of ressource issue

The simplest way BY FAR would be to remove the #N/A errors in the first place.... =IFERROR(formula;0)

Then a straightforward SUMPRODUCT will work fine.

Yeah, I did tried that a few months ago, but when the cell was empty, it led to other issues in my data table but I can't remember which ones...

Moreover...assuming it works, will it really make my file less memory-intensive ?

EDIT : i sort of remember...the things is cannot input zero as an error as it is an admissible value in my data table (basically, I have growth rates of macro variables in different countries on which I make calculations). Hence I tried "" to have an empty cell, but I think it led to some mistakes when I was trying to do my product...

Your formula is only looking at 22 columns. If it was looking at 220,000... then maybe. Your performance issue lies elsewhere, methinks.

Thats beginning to get big....

"Excel cannot complete this task with available resources..."

This very rarely if ever is attributed to formulas. Its usually a matter of excess content in the file and/or how a user tries to do something (ex: copy/paste whole columns/rows/sheets instead of data range).

EDIT: forgot to mention. Formulas generally will just increase calc time, not give an error. In the status bar it should display something like % calculating on x threads or something to that effect. If you have a complex calculation tree this can take a while, but ive never seen formulas alone give the out of resources error

SUMPRODUCT is not as efficient as IFS functions (SUMIFS, COUNTIFS, AVERAGEIFS) but it is more flexible and still faster than SUM(IF array formulas.

This link from MS details array formulas and lookups. https://msdn.microsoft.com/en-us/vba...rraySumProduct

Can you post a sanitized sample file?

If not try the following:

Open a VBA window with that file open (alt+f11). At the bottom you should have a pane titled "immediate window", if not Ctrl+G. In the immediate window type the following (hit enter after typing in each bullet point line without the bullet):

• ?Activeworkbook.names.count
• ?Activeworkbook.styles.count

This will give you a count of the styles and named ranges in the file. There are 47 styles by default. You may have a ton of either.

Also, on each sheet the used range should end at the bottom of scrolling down, end of scrolling right. In other words when you scroll on a sheet you should NOT zip past your information seeing blank cells. A good way to test used range on each sheet is to select cell A1 then use the shortcut ctrl+shift+end. This selects what excel thinks is the used range on a sheet. It should end at the intersection of the last column and last row you actually have content in. If not, you have excess content.

Just a glance and I can tell you have excess cells on at least some sheets. On IP you go way past rows with data and have a fill going in a full column. Clear out the excess on your sheets and I wont be surprised if you have fewer or no errors (again depends on if people are doing something that triggers a problem).

EDIT: other sheet goes to column WXA, just a tad past your data range

Couple things that are likely much slower than SUMPRODUCT:

On IP sheet:

Formula:

Please Login or Register  to view this content.

you have a bunch of lookup formulas referencing entire columns. look for an alternative to lookup and reference only your data range

Formula:

Please Login or Register  to view this content.

this could likely be a SUMPRODUCT, maybe a combination of SUMIFS and something. Array entering like this is slow (SUM(IF array or variations of it). You have a bunch of these too. You have variations of SUM(IF arrays throughout the file, you should consider updating them if possible to something like SUMPORODUCT or a combination of IFS functions (SUMIFS, COUNTIFS, AVERAGEIFS).

Your sample has over 2500 array entered formulas. When possible, using array like formulas like *IFS and SUMPRODUCT will be much faster.

EDIT: in your sample file your largest component sizes are:

• The 2 sheet xml files
• your calc chain xml

Clearing out excess may reduce the sheet sizes, it likely one or more are significantly larger than they should be due to excess content. Once you alter your formulas as mentioned above I would recommend rebuilding your calc chain (ctrl+alt+shift+f9).

Thanks for all the suggestions.

Some questions/remarks :

1. The lookup function on a column was to retrieve the last numeric value of said column (where there can be blanks, numeric and non-numeric values). That was the only way I found to do so. And I recall having a bunch of those in other worskheets without any issue. Anyhow, would you happen to know of a more efficient way to retrieve the last value of a column ?

2. Yeah,I did not use sumproduct because the cells are not contiguous and depend on the "region" the country is in (Europe, LatAM...). But since I have a line with those regions, I guess a sumifs with a condition on the regions should work as well.

3. thanks I will do that. I have tried downloading a addin to remove excess content but it did not seem to work. I guess I can do it manually. Sorry but what's a calc chain ?

Originally Posted by lillumultipass

And I recall having a bunch of those in other worskheets without any issue.

Just because it worked before doesnt mean its not contributing to the issue. Thats like a burglar getting caught and going "well I used to get away with it".

If its excess cells, which I have already confirmed you have some, then what happens is the range you actually have data in is smaller (sometimes much smaller) than the actual used range (the range Excel thinks has contents). So even in a formula that may be aware of the used range and not waste resources on a full column reference past the used range, due to the excess cells your used range could be much greater than your actual range adding time to your calculations.

The analogy I use is this. Imagine I ask you to go out to a parking lot with 10 marked spots (each has a unique ID) and ask you to remember the spots that have cars in them...easy enough. Now lets say I actually want you to remember not only the ones that have cars in them, but all the empty ones as well...thats harder (this is essentially what we ask of Excel, manage our data and manage the empty cells in our used range...the greater the used range the harder it is).

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.