Another method to speed up SUMPRODUCT

[CelestialMind7]

Hi guys,

first time poster, long time lurker. First of all I want to thank you all for your awesome contributions. Thanks to you guys my vba skills have improved and I was able to find solutions to many questions I had in the past.

Having said that, I've searched and searched for the solutions for this but what I've found hasn't worked for me.

I've developed a very large and fairly complex reporting tool for my company that generates metrics and other reports for the management. There is one part that slows down the entire report and it's the SUMPRODUCT formula. Is there another way without doing a concatenate like helper column? SUMIFS doesn't work btw. Here's the formula:

=SUMPRODUCT(--(Consolidated!N:N=A2)*(Consolidated!K:K="Pending Action"),--(Consolidated!V:V>0),--(Consolidated!V:V<>""))

One column is doing a calculation from a raw consolidated data tab that's around 200k rows which is why it takes quite a long time to complete.

 

[CelestialMind7]

One thing I should probably mention, I simply replaced SUMPRODUCT with SUMIFS so something tells me the rest of the formula is wrong since this is originally specifically designed for a sumproduct layout.

 

[Canapone]

Hi,

sumproduct is much slower than sumifs and countifs

https://exceloffthegrid.com/optimize-formula-calculation-speed-part-8-sum-with-multiple-criteria/

https://www.mrexcel.com/forum/excel-questions/561914-sumifs-vs-sumproduct-calc-time.html

You could try

=Countifs(CONSOLIDATED!N:N,A2,CONSOLIDATED!K:K,"Pending Action",CONSOLIDATED!V:V,">0",CONSOLIDATED!V:V;"<>")

or you could try to reduce ranges read by SUMPRODUCT


=SUMPRODUCT(--(Consolidated!N$1:N$200000=A2),--(Consolidated!K$1:K$200000="Pending Action"),--(Consolidated!V$1:V$200000>0),--(Consolidated!V$1:V$200000<>""))

Regards

 

[CelestialMind7]

Hi,

sumproduct is much slower than sumifs and countifs

https://exceloffthegrid.com/optimize-formula-calculation-speed-part-8-sum-with-multiple-criteria/

https://www.mrexcel.com/forum/excel-questions/561914-sumifs-vs-sumproduct-calc-time.html

You could try

=Countifs(CONSOLIDATED!N:N,A2,CONSOLIDATED!K:K,"Pending Action",CONSOLIDATED!V:V,">0",CONSOLIDATED!V:V;"<>")

or you could try to reduce ranges read by SUMPRODUCT


=SUMPRODUCT(--(Consolidated!N$1:N$200000=A2),--(Consolidated!K$1:K$200000="Pending Action"),--(Consolidated!V$1:V$200000>0),--(Consolidated!V$1:V$200000<>""))

Regards

Thank you Canapone, I actually figured it out before I got a chance to look back into this thread
SUMIFS solved my problem along with COUNTF in the other column. went from almost 2 hours down to 25 minutes flat. Very happy about that.

 

[Canapone]

Ciao,

thanks for your kind feedback

I also use often SUMPRODUCT: more flexibility, more versatile...