Excel formula slows down Excel spreadsheet

1
votes

I have been applying this formula in my Excel spreadsheet but when I enter this formula in multiple cells, my Excel calculating processor slows down and eventually crashes. Is there any way to make this formula work faster?

Purpose of formula: To count filtered data with 2 conditions (Y being positive and subject name being X) applied within the formula.

Multiple cells as what I mean is that Dryness + absorbency is one of the fields. There are about few fields for different brands ( Brand A , Brand B , Brand C)

=SUMPRODUCT(
--(INDEX('Current Month'!$A:BJ,0,MATCH("Subject Name",'Current Month'!$A$1:$BJ$1,0))="Pampers"),
--(INDEX('Current Month'!$A:BJ,0,MATCH("Dryness + Absorbency",'Current Month'!$A$1:$AJ$1,0))="POSITIVE"),
--(IF(SUBTOTAL(103,INDIRECT("'Current Month'!$A"&ROW(A:A)&":$A"&ROW(A:A))),1,0)=1)
)

Hope someone can help as I have tried multiple times and my Excel keeps crashing. Thank you.

2
excelexcel-formulavba
you could cut down the full column references and change the volatile indirect to a non-volatile index. sample data with expected results needed to assist further. - user4039065
Hi Jeeped, I have added the dummy file in the question. Thanks for the reply. - Faheera
Did you replace the INDIRECT with INDEX? Indirect has a big overhead because they are recalculated every time recalculation is triggered - even if they don't need to be. - Mark Fitzgerald
I've done some speed testing and I am approaching an answer but I am still unclear on why you use the column BJ and AJ in the same function. Also why is column A absolute (e.g. $A) and column AJ/BJ is relative (AJ/BJ can be filled right for AK/BK, etc). I'm also unclear why you would post 'sample data' complete with (limited) narrative that does not correspond with your question's narrative. - user4039065

2 Answers

1
votes

The reason excel is crashing is because:

INDEX('Current Month'!$A:BJ

That would be an array of over 65 million cells.

Name your array, only the rows needed, and use that instead of 'Current Month'!$A:BJ. I didn't examine the rest of your formula, but this is where I would start. Same goes for the rest of your arrays.

0
votes

You need to restrict the array in your formula.

=SUMPRODUCT(SUBTOTAL(3,OFFSET('Current Month'!A1:A32,ROW('Current Month'!A1:A32)-MIN(ROW('Current Month'!A1:A32)),,1))*--(INDEX('Current Month'!$A1:BJ32,0,MATCH("Subject Name",'Current Month'!$A$1:$BJ$1,0))="Pampers")*--(INDEX('Current Month'!$A1:BJ32,0,MATCH("Dryness + Absorbency",'Current Month'!$A$1:$AJ$1,0))="POSITIVE"))