SUMPRODUCT attn arvi

[samilator]

Arvi I’m having trouble following your suggestion. I’m also havin
trouble making my questions but I think I’ve finally got it right.

Let me rephrase my question:
I’ve got two sheets. Sheet1 = sell. Sheet2 = sell history.
Sheet1:
Column a = date
Column b = customer
Column c = branch
Column d = total ordered
Column e = stock no.
Column f = size

Sheet2:
Column a3:a5000 = stock nos.
Column b3:b5000 = sizes
Row C1:Z1 = customer
Row C2:Z2 = branch

Now C3 would should display the corresponding total ordered for th
intersecting conditions… as should the other cells

This is the formula I used for C3
SUMPRODUCT(--(Sheet1!B2:B5000=Sheet2!C1),--(Sheet1!C2:C5000=Sheet2!C2),--(Sheet1!E2:E5000=Sheet2!A3),--(Sheet1!F2:F5000=Sheet2!B3),Sheet1!D25000)

It works fine but when you’ve got too many of them excel freezes.
Is there another way around this?

Thanks again

 

[Arvi Laanemets]

Hi

At start: Keep your messages in same thread! Most of us, when answering a
question, do activate Watch for thread. There was almost no chance your 2nd
posting was noticed by me.

You have a block of 23*4997=114931 formulas - and complex ones. And as
butter on cacke, they all do use ranges as arguments. Your comp simply
doesn't have memory enough to cope with this. So you have better to cut down
this number! Or use different ways. Btw, did you try another advice you
got - Pivot table?

Did you try to define named ranges for columns in Sheet1, and use them in
SUMPRODUCT formula? When yes, how did it work.

 

[samilator]

oops im sory... ill just use this thread

i have a P3 800MHz w/ 192MB ram... its quite old but i guess should b
able to handle it... and i checked my task manager while runing excel.
the memory usage is about 10-20MB only.. but the CPu usage is abou
100%..

i tried named ranges but its still the same..

im thinking of dividing the conditions... so that my computer will no
be burdened so much.. is it possible to filter first the row tha
satisfy the first conditon then store it somewhere like anothe
wokrsheet then filter the new worksheet with the second condtion the
store it then filter it with the third contion then do the summation.
that way it would not filter the 3 conditons same time.. its a longe
solution but atleat it might not freeze... would this work?? do yo
know how to do it or i cant be done in excel