A single SUMPRODUCT Excel formula cover four worksheets

[wilchong via OfficeKB.com]

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

 

[Roger Govier]

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

 

[wilchong via OfficeKB.com]

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

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

 

[Roger Govier]

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

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

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

 

[Shane Devenshire]

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