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