SUMPRODUCT attn arvi

Arvi I’m having trouble following your suggestion. I’m also having
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 the
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.


---
Message posted from http://www.ExcelForum.com/

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.


--
Arvi Laanemets
(When sending e-mail, use address arvilAttarkon.ee)


"samilator " wrote in message
...
Arvi I’m having trouble following your suggestion. I’m also having
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 the
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),--(Sh
eet1!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.


---
Message posted from http://www.ExcelForum.com/

oops im sory... ill just use this thread

i have a P3 800MHz w/ 192MB ram... its quite old but i guess should be
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 about
100%..

i tried named ranges but its still the same..

im thinking of dividing the conditions... so that my computer will not
be burdened so much.. is it possible to filter first the row that
satisfy the first conditon then store it somewhere like another
wokrsheet then filter the new worksheet with the second condtion then
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 longer
solution but atleat it might not freeze... would this work?? do you
know how to do it or i cant be done in excel?


---
Message posted from http://www.ExcelForum.com/