Cumulative selective sum using sumproduct - how to speed up?

[cesarez]

Hi all,
I am trying to do the following:
- I have a list of items (say cars), one on each line, and attached there is the name of the owner and the value of the car
- I need to pick up the first n most valuable cars for each owner up to a value of x
- every car that makes me exceed the total value of x has not to be picked up

My attempt:
- List the cars from the most expensive to the cheapest
- For each line sum the value of the previous cars belonging to the same owner
- So you have a cumulative sum *for each owner*
- In a second column, I flag the lines in which the value exceeds x

Seems simple but excel experiences huge difficulties in calculating the cumulative sum, probably because I am working on more than 20k lines.
It takes ages to calculate and I never managed to paste values to save my work.

I tried to obtain this result using a sumproduct [=SUMPRODUCT((D13=D13:$D$13)*(AB13:$AB$13))] where column D is the name of the owner and column AB is the value of the car.
I tried to do the same using sumif, but seems even worse.

I am running excel 64bit on a i7 with 8gb RAM, so the HW/resources should not be the problem.
Is there a smarter way to do this without using VBA?

Hope you can help me because this issue is really blocking me....
Thanks a lot
C

 

[Aladin Akyurek]

Not true anymore,
it worked once using sumif, now I am not able to paste values anymore....

I don't understand what you mean. Care to enumerate step by step what you are doing?

 

[cesarez]

Sorry, my fault. It wasn't actually understandable

I paste the formula (the problem is the same both using sumproduct and sumif) and press f9 to calculate.

It takes a while but works.

Then i usually copy and paste values -instead of keeping formulas - when i do not need dinamic cells in a model so as to avoid excel to recalculate every cell all the times. When i paste values, xls freezes.

Hope it is more clear. Thanks for your help!

 

[Robert Mika]

Instead of pressing F9 try this macro:

<code>Sub Recalc()
Selection.Calculate
End Sub</code>

Higlight only the region you want to recalcute and run it.
You have got 2M rows so Excel may freezee.
Try to copy to another workbook.

 

[cesarez]

If I understand well te macro is aimed to avoid recalculating all the sheet. I have to say that the problem actually is NOT calculating, since xls manages to do so without freezing. The problem is copy-pasting values after the calculation to keep the model light.

If you meant I should always use the macro from now on, so I do not have the need to paste values to avoid useless recalculations, I suppose it is not very feasible....

Regarding the last suggestion (copy to another workbook) it is not very easy to do, since cells in the sheet refer to other 10 tabs in the same worksheet... Do you think there might be some bug that would be fixed copying in another book?