Substitute for Sumproduct

[trone77]

Needing to replace the formulas I have with my workbook due to slow calculation and saving times, I was wondering if I could use for instance COUNTIFS instead of the SUMPRODUCT without the use of an array formula. The formula I'm looking to replace is listed below:

=SUMPRODUCT(('[AY.xlsx]1998-2015'!$X$1:$X$8766=$K5)+('[AY.xlsx]1998-2015'!$X$1:$X$8766=$L5),--('[AY.xlsx]1998-2015'!$B$1:$B$8766=N$2),--('[AY.xlsx]1998-2015'!$W$1:$W$8766=$J5),--('[AY.xlsx]1998-2015'!$Y$1:$Y$8766=N$3))

 

[t0ny84]

What about just using SUM()?

Is your workbook AY.xlsxor is this one you are just grabbing data from?

 

[Peter_SSs]

The options available to you could depend on your Excel version(s). I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)