Speeding up SUMPRODUCT/ SUMIFS array formula

Hi all,

I have a large data set where I need to SUM cells IF a certain criteria is met without using helper columns. The formula I am currently using is giving the right results, though, as it is a SUMPRODUCT/ SUMIFS array formula it makes the file unnecessarily slow. I have already speeded it up by using:

Please Login or Register  to view this content.

at the beginning of the formula to not have it calculate the array if not relevant, this speeds it up by about a factor of 2 in my data set.

Please Login or Register  to view this content.

Now I was wondering if there is another "trick" to make it ignore calculations if not relevant. E.g. Only SUMIF data from the "IA" sheet if criteria is met and "skip" calculation of cells in the array that aren't relevant. Currently even if ALL data in the GREY cells is deleted, calculation times don't change!! The formula still seems to run through every single cell in the array and calculate the SUM of all PRODUCTS. Is it possible to rewrite the formula somehow so that it skips these cells? Or use a different approach/formula altogether?

BTW I am currently testing the speed with a macro. Select the cells that need testing and press CONTROL+SHIFT+R. It gives me about 0.17 seconds for all "MIN IA" cells (29 cells in total). In my data set it's about 10 seconds as the array gets longer.

I had tested that already, it makes NO difference or whatsoever! It seems to ignore the blank cells anyway, just like in any other SUMIF without SUMPRODUCT...

Originally Posted by FlameRetired

Still not certain I get what you are wanting. I was addressing the speed issue.

I was referring to the speed as well! I tried A7:ZZ409 etc. in the SUMIFS part and get the same speed result.

My intent is to SUM the data in the "IA" sheet for all cells on the "Range" sheet which contain either an "x", "xx", "xxx", " x", " x " etc., all numbers greater than zero, and ignore all cells where the attribute says "DROP" or "INACTIVE"

Just tested it, it returns 14 indeed, but 14 is wrong! As I said, the results I am getting are correct with my original formula, it is just the speed that makes my file 10 seconds slower than it should...

The first part of the formula:

Please Login or Register  to view this content.

just focuses on the numbers in the array and ignores the ones below zero

Please Login or Register  to view this content.

The second part:

Please Login or Register  to view this content.

is evaluating the "x"s, basically it checks the array for cells with "x", "xx", or "xxx"

Please Login or Register  to view this content.

and substitutes cells that contain blanks such as "x ", " x", " x "

Please Login or Register  to view this content.

otherwise it would neglect them which it shouldn't. So if the result is >0 (either number or x's) it counts these cells as TRUE. Don't get hung up to much with the ISNUMBER in the second part, that confused me too. For some reason it returns TRUE if the cell is not blank when you combine it with the these other formulas, maybe someone else can explain why... I'm sure there's a better way (less confusing) to write the formula.

So in the end, both numbers (positive) and x's are counted (maximum three x's) in the formula, and it then SUMs the numbers from the "IA" sheets for these cells with the corresponding Attribute. It also ignores all items that are Dropped or Inactive

Please Login or Register  to view this content.

Originally Posted by Zer0Cool

*755 named ranges, most of them resulting in ref errors...bad for speed. Clean up your named ranges.

Since I had to reduce the file size, I obviously removed everything that wasn't relevant to the issue. Named ranges are clean and none of them result in #REF!

*Applying a style to every cell on a sheet, also bad for speed and file size. Likely to eventually lead to duplicate/excess styles. I am already seeing signs of corrupt styles in the file. As an example, the styles list on the home tab should not change in terms of the styles available regardless of where you are in the file...yours does. E1 and E2 for example list a different set of styles. Not a good sign.

Also not the case in my original file, but a good call out that I will keep in mind when developing spreadsheets in the future...

*Remove formulas that are not needed or result in errors. Column A on the Range sheet is all #REF (maybe due to the named range it uses being ref, which may point at an external file I dont have)

Again, not a #REF in my file and an essential formula, already removed as much as I could, that's why I am focusing on improving formulas now rather than deleting them...

I am still reviewing the formula...the INDEX in it using full column/row ranges is likely not good, ill post back soon

As you mentioned in your previous post, Excel's IFS functions can efficiently handle whole column/row references...

Originally Posted by Zer0Cool

See if this formula is more efficient:

Formula:

Please Login or Register  to view this content.

I got the same results as your formula but simplified the considerations for x,xx,xxx,DROP and numeric. I also used the INDEX with a reference to just your data range.

As the sample is small I didnt bother with speed testing it, as all results would be within margin of error on the timer. See how it compares in your actual data set.

Tested your formula and getting no difference in speed not even .001%. Excel seems to handle the whole columns very efficiently indeed.