SUMPRODUCT vs SUMIFS Efficiency???

Question

Excel 2010 ...

My Formula below is working fine ... However, for the amount of records I have would a SUMIFS variant be more efficient???

If So ... What would that SUMIFS variant be???

If No ... Then I am good to go ... :)

=SUMPRODUCT(--('WS1'!$C$2:$C$40000=$C5),--('WS1'!$D$2:$D$40000=$D5),--('WS1'!$J$2:$J$40000=G$3),'WS1'!$K$2:$K$40000)

Thanks ... Kha

Answer 1

SUMIFS is probably more efficient. Try this:

=SUMIFS('WS1'!$K$2:$K$40000,'WS1'!$C$2:$C$40000,$C5,'WS1'!$D$2:$D$40000,$D5,'WS1'!$J$2:$J$40000,G$3)

Answer 2

Vijay ... (Hi)

Hans SUMIFs solution worked Great ...

However, I also wanted to thank you for supporting these boards where so many solutions are found by those of us less intimate with Excel (in particular ... Me)

Thank you for the further insight you have provided here ... Kha

Answer 3

Hans ... (Hi)

Once again you have solved my problem ... I had a feeling SUMIFs might be faster, but was up against it this morning & just didn't have time to play with SUMIFs ... so I continued to use my SUMPRODUCT ...  However, once the dust settled I revised my Formula per your SUMIFs solution ...

Works GREAT ...

Thank you for supporting these boards ... Kha

Answer 4

SUMIF is definitely faster than SUMPRODUCT.

If you want to make it all the more faster, hold these conditions in different columns  and perform (SUMIF or SUMPRODUCT which is slower than SUMIF). This will speed up for both SUMIF and SUMPRODUCT.

Hence, your one column may hold =C2 = $C$5, other may have D2=$D$5 and so on..You need to just check resulting 1 / 0 or TRUE and FALSE in your SUMIF and SUMPRODUCT. Hence your SUMPRODUCT will say

=SUMPRODUCT(--(WS1!M2:M40000 =TRUE)....................)

=SUMIFS('WS1'!$K$2:$K$40000, 'WS1'!$M$2:$M$40000,TRUE........)

Assuming column M is first helper column.

You have done rightly by using , syntax rather than * syntax as , one is around 10 - 20% faster than * syntax.