If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
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/ |
#3
|
|||
|
|||
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/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using a named range in a Sumproduct comparison | Hari | General Discussion | 7 | August 23rd, 2004 06:48 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |
Sumproduct and zero's..help please | www.ttdown.com | Worksheet Functions | 6 | March 21st, 2004 01:19 PM |
If & SumProduct | Otto Moehrbach | Worksheet Functions | 6 | November 14th, 2003 11:43 AM |