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
|
|||
|
|||
A single SUMPRODUCT Excel formula cover four worksheets
I have four worksheets as follow: PC, OZ, IM and KL; each of the worksheet is
containing data. In another separate worksheet is performing the analytic function. In this worksheet, only one type of excel formular is applying: SUMPRODUCT(--(x=x),-- (y=y),--(z=z),(A:A). Because there are four worksheets, I need to set four formula as following in one cell: SUMPRODUCT(--(x=x),--(y=y),--(z=z),(A:A)+SUMPRODUCT(--(x=x),--(y=y),--(z=z), (A:A)+SUMPRODUCT(--(x=x),--(y=y),--(z=z),(A:A)+SUMPRODUCT(--(x=x),--(y=y),-- (z=z),(A:A) I feel above formula is a very very long formula and make the size of the Excel file very huge. My question is that is there any way to make ONE Excel formula to cover the four worksheets and at the same time still allow to perform SUMPRODUCT function? Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
#2
|
|||
|
|||
A single SUMPRODUCT Excel formula cover four worksheets
Hi
You could carry out the Sumproduct on each individual sheet in an area to the right of your data, say cell X1 onward Then create 2 new sheets called First and Last, and drag these to a position which "sandwiches" your 4 data sheets e.g. Summary, First PC, OZ, IM, KL, Last On the summary sheet then you can just use =SUM(First:Last!X1) The sheets First and Last can be hidden once you have placed them in their correct location -- Regards Roger Govier "wilchong via OfficeKB.com" u43231@uwe wrote in message news:8439538dffe8a@uwe... I have four worksheets as follow: PC, OZ, IM and KL; each of the worksheet is containing data. In another separate worksheet is performing the analytic function. In this worksheet, only one type of excel formular is applying: SUMPRODUCT(--(x=x),-- (y=y),--(z=z),(A:A). Because there are four worksheets, I need to set four formula as following in one cell: SUMPRODUCT(--(x=x),--(y=y),--(z=z),(A:A)+SUMPRODUCT(--(x=x),--(y=y),--(z=z), (A:A)+SUMPRODUCT(--(x=x),--(y=y),--(z=z),(A:A)+SUMPRODUCT(--(x=x),--(y=y),-- (z=z),(A:A) I feel above formula is a very very long formula and make the size of the Excel file very huge. My question is that is there any way to make ONE Excel formula to cover the four worksheets and at the same time still allow to perform SUMPRODUCT function? Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
#3
|
|||
|
|||
A single SUMPRODUCT Excel formula cover four worksheets
Hello Roger,
Many thanks for your advice! I understand your strategy. However, according to your way, it will create more worksheets and make the size of the file is much larger! I want a formula which really make thing simple. Ideally, the SUMPRODUCT can sum up the data from various worksheets WITHOUT create extra worksheets and at the same time reduce the size of the file! That is what I want! Many thanks for your effort! Wilchong Roger Govier wrote: Hi You could carry out the Sumproduct on each individual sheet in an area to the right of your data, say cell X1 onward Then create 2 new sheets called First and Last, and drag these to a position which "sandwiches" your 4 data sheets e.g. Summary, First PC, OZ, IM, KL, Last On the summary sheet then you can just use =SUM(First:Last!X1) The sheets First and Last can be hidden once you have placed them in their correct location I have four worksheets as follow: PC, OZ, IM and KL; each of the worksheet is [quoted text clipped - 22 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
#4
|
|||
|
|||
A single SUMPRODUCT Excel formula cover four worksheets
Hi
Adding 2 blank sheets to the file will make a negligible difference to file size - about 3Kb It is when you add more data or more formulae that the file size increases. Try it and see. -- Regards Roger Govier "wilchong via OfficeKB.com" u43231@uwe wrote in message news:843b2029ba9f0@uwe... Hello Roger, Many thanks for your advice! I understand your strategy. However, according to your way, it will create more worksheets and make the size of the file is much larger! I want a formula which really make thing simple. Ideally, the SUMPRODUCT can sum up the data from various worksheets WITHOUT create extra worksheets and at the same time reduce the size of the file! That is what I want! Many thanks for your effort! Wilchong Roger Govier wrote: Hi You could carry out the Sumproduct on each individual sheet in an area to the right of your data, say cell X1 onward Then create 2 new sheets called First and Last, and drag these to a position which "sandwiches" your 4 data sheets e.g. Summary, First PC, OZ, IM, KL, Last On the summary sheet then you can just use =SUM(First:Last!X1) The sheets First and Last can be hidden once you have placed them in their correct location I have four worksheets as follow: PC, OZ, IM and KL; each of the worksheet is [quoted text clipped - 22 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
#5
|
|||
|
|||
A single SUMPRODUCT Excel formula cover four worksheets
Hi,
There are a couple of other things you can do: 0. You don't need parenthesis around the A:A references. This will reduce the formula down by 8 characters. 1. Consider range names: Since the last range of your formula reflects an entire column the other three references must also reflect entire columns, so you should define range names for each column, the shorter the better, for example, E, X, Y, Z, F, M, N, O, G, T, U, V, H, J, K, L and A, B, D. X, Y, and Z represent the columns you are testing on the PC sheet, and E is the numeric column on that same sheet. A, B, and D are the names of the cells on the summary sheet which contain the values you are testing against. If there are a lot of these you can either leave cell addresses in or define the range as an array. By doing this the formulas reduce from things like PC!X:X=a1 to X=A. This reduces the overall formula from =SUMPRODUCT(--(PC!x:x=A1),--(PC!y:y=B1),--(PC!z:z=C1),(A:A))+SUMPRODUCT(... to =SUMPRODUCT(--(X=A),--(Y=B),--(A=D),E)+SUMPRODUCT(... 2. Replace -- with the N function: This reduces the formulas from =SUMPRODUCT(--(X=A),--(Y=B),--(Z=D),E)+SUMPRODUCT(... to =SUMPRODUCT(N(X=A),N(Y=B),N(Z=D),E)+SUMPRODUCT(... Which are actually 12 characters shorter. but then you could 3. Combine multiple operations within one SUMPRODUCT This reduces the formulas from =SUMPRODUCT(N(X=A),N(Y=B),N(Z=D),E)+SUMPRODUCT(... to =SUMPRODUCT(N(X=A)*N(Y=B)*N(Z=D)*E+N(M=A)*N(N=B)*N (O=C)*F+... This eliminates 3 of the 4 SUMPRODUCT functions, reducing the formula by 30 characters (actually it also eliminates the need for both an () parenthesis for those 2 components, so its really 36 characters shorter. 4. You could enter it as an array and reduce the formula by another 7 characters but the calculations will be slower even if the file size is smaller and the formula length shorter. The final formula would be something like in its entirety: =SUMPRODUCT(N(X=A)*N(Y=B)*N(Z=D)*E+N(M=A)*N(N=B)*N (O=C)*F+N(T=A)*N(U=B)*N(V=C)*G+N(J=A)*N(K=B)*N(L=C )*H) This is probably far shorter than what you are currently using, even though its not short. Even if you use addresses on the summary sheet for A, B, and D references, the formula still remains shorter by far. (I did not use C as a range name because it is reserved by Excel). There are other things that could be done also, depending on the layout of the summary page. Cheers, Shane Devenshire Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:8439538dffe8a@uwe... I have four worksheets as follow: PC, OZ, IM and KL; each of the worksheet is containing data. In another separate worksheet is performing the analytic function. In this worksheet, only one type of excel formular is applying: SUMPRODUCT(--(x=x),-- (y=y),--(z=z),(A:A). Because there are four worksheets, I need to set four formula as following in one cell: SUMPRODUCT(--(x=x),--(y=y),--(z=z),(A:A)+SUMPRODUCT(--(x=x),--(y=y),--(z=z), (A:A)+SUMPRODUCT(--(x=x),--(y=y),--(z=z),(A:A)+SUMPRODUCT(--(x=x),--(y=y),-- (z=z),(A:A) I feel above formula is a very very long formula and make the size of the Excel file very huge. My question is that is there any way to make ONE Excel formula to cover the four worksheets and at the same time still allow to perform SUMPRODUCT function? Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
Thread Tools | |
Display Modes | |
|
|