Sumproduct alternative

[Alph0427x]

Hello,

I'm working on a relative big file, almost 14,000 cells which all of'em contain sumproduct...

As you would expect, its very slow. I would like to know if there's an alternative to sumproduct for this particulare case...



This is my formula:

---

=SUMPRODUCT(SUMIF(Finanzas!$G$3:$G$20000,CONCATENATE('Importe T. x Área'!$O5:$S5,"-",'Importe T. x Área'!W$3),Finanzas!$O$3:$O$8706))

---

Finanzas!$G$3:$G$20000 - Criteria Range
CONCATENATE('Importe T. x Área'!$O5:$S5,"-",'Importe T. x Área'!W$3) - Criteria (Array)
Finanzas!$O$3:$O$8706 - SumRange


Thanks for the help!

 

[JackDanIce]

Welcome to the board.

You shouldn't need to use a SUMIF inside a SUMPRODUCT function, optimising this I believe will improve the calculation speed.

It's not clear what your formula is doing, especially with the Criteria part, take a read of:

Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online

It also contains a great example of using SUMPRODUCT and should help you remove the SUMIF component, good luck!

 

[Aladin Akyurek]

In...

=SUMPRODUCT(SUMIF(Finanzas!$G$3:$G$20000,CONCATENATE('Importe T. x Área'!$O5:$S5,"-",'Importe T. x Área'!W$3),Finanzas!$O$3:$O$8706))

the condition range and the sum range are not equal in size!

Does the following yields some improventment in speed?

=SUMPRODUCT(Finanzas!$O$3:$O$8706,--ISNUMBER(MATCH(Finanzas!$G$3:$G$8706,'Importe T. x Área'!$O5:$S5&"-"&'Importe T. x Área'!W$3,0)))