Sumproduct runs to long

[saarit]

I have a sumproduct formula but it takes to long to run. How can I rewrite it or use index match match to speed this thing up?

=SUMPRODUCT(($C$2:$C$200000=[@InvoiceNumber])*($D$2:$D$200000=[@LineNumber])*$F$2:$F$200000)

It calculates 2, 200000 line arrays and then returns the amount requested, column F(Line Amount) when it hits the (1,1,Line Amount) result and the other 199999 lines are discarded(returns 0 values if not the right invoice or the right line). This will take 200 minutes to process when I copy this down for all 200000 lines with my current computer hardware.

I can see that if I could only search line numbers for that particular invoice it would speed up the formula greatly. Would I have to nest a function and then run as an array formula? Produce a temp. array with all invoice number, line numbers and then just pull the appropriate line amount from that temp. array.
Any help would be great.

 

[Somendra Misra]

@saarit

See hows the performance of only SUM function with IF in it.

Regards,

 

[Luke M]

Do you really need all 200,000 lines? It's the size that's killing you. ALong that, why would you want to copy the formula down??

I think a better use would be to create a PivotTable. Let you easily see/show all the InvoiceNumber and LineNumber combinations, and get the totals. Much less calculation intensive.

 

[saarit]

I have 2 data sources with 200k lines each and I have to build an integrity report down to each line to find any difference and then track those down and fix them.

 

[PeterB]

Please can you upload your source file so we can see. At this stage, I agree with Luke M. A pivot table is almost certainly the way to go.

 

[NARAYANK991]

Hi Saarit ,

Speeding up is easily possible if you use helper columns !

Speeding up is also possible if you can confirm that all the lines pertaining to one invoice are together , so that you first look for the invoice , and when a match is found , look for the lines only in the few rows from that point downwards.

There is a series of blog posts on this here :

http://fastexcel.wordpress.com/2011...ps-part-1-using-excels-functions-efficiently/

Narayan

 

[saarit]

I'll just cheat and combine my invoice and line numbers into a unique identifier in my datasets and run an index match. Similar to your helper column idea.

So Invoice + (Line Number/1000) = Unique Identifier, copy down in both spreadsheets, couple of seconds.
Invoice(desired value, match(unique, dataset2, 0)), 5 minutes to copy down.

Now I can run the combined datasets in a pivot table to kick out my integrity report and get to work.

I just needed some lunch in me.