Multiplicative reference across worksheets

[Oppie]

I know that I can use 3-D features to reference across multiple
worksheets. For example, if I want to SUM all the values in field D4 for
worksheets '010' through '025', I write:

=SUM('010:025'!D4)

Is there a way I can SUM the products of fields D4 and G4 for the same
sheets?

I tried the following, but this isn't accepted as legitimate format:

=SUM('010:025'!(D4*G4))

I've tried several other combinations. Does anyone know how to do this
(if in fact it can be done)?

Thanks!

 

[Microsoft Forum]

Hi Oppie,

Try to use SUMPRODUCT function to do the job.

Frederick
Hong Kong.
c

 

[JulieD]

Hi

try
=PRODUCT(010:025!D4)

cheers
JulieD

 

[Earl Kiosterud]

Oppie,

Why not put a formula for the products in each sheet, like in H4:

=D4 * G4

Then sum them with:

=SUM('010:025'!H4)

You can hide column G

 

[Oppie]

I tried using SUMPRODUCT and that didn't work. My equation was

=SUMPRODUCT('010:025'!D4,'010:025'!G4)

This gives me a Reference error.

Am I using SUMPRODUCT incorrectly?

 

[Oppie]

Julie,

This gives me the product of all the D4's across the worksheets. What I
want to do is get the SUM of the PRODUCTS of D4*G4 across the worksheets.

I tried using SUMPRODUCT but I get a reference error.

Oppie

 

[Oppie]

Yeah .. I may have to do that. The example I gave is just one piece of
it. For me to do all the sum-products I want to do, I would have to put
the products in each worksheet (of which there are many) and would have
to do this for many items per worksheet. That's why I'm looking for the
lazy-way out of this mess! LOL