Re: 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 ***, and E is the numeric column on that same ***. A, B, and D are the names of the cells on the summary *** 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 *** 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@xxxxxx
I have four worksheets as follow: PC, OZ, IM and KL; each of the worksheet is
containing data.

In another separate work*** is performing the analytic function. In this
work***, 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.aspx/excel-new/200805/1

.