sumproduct in 3D

[Andrew Hodgson]

I have to perform a consolidation across multiple subsidiaries each with
multiple line items and periods but every company has the same layout on
separate sheets.

The sum and product function work well in 3D across the sheets but is there
a sumproduct type function that can be applied in 3D without having to
reference each individual sheet name working in a similar that the sum
command works in 3D

Eg
Sheet 1 : Co 1, Sheet 2 : Co 2 ,Sheet 3 Co 3 ... Sheet 20 : Co 20

Sheet 1 : Co 1
Column A B C D
2006 2007 2007
Row 2 Shareholding 75% 75% 80%
Row 3 Revenue 100 150 200
Column A B C D

Sheet 2 : Co 2
2006 2007 2007
Row 2 Shareholding 90% 90% 90%
Row 3 Revenue 500 1000 1200

On a consolidation sheet I need a formulae that would first multiply the
shareholding by the revenue for each subsidiary and then add up the 20
subsidiaries. In the example above in cell B3 the consolidated revenue would
include the 75% of Co 1 revenue of 100 + 90% of Co 2 500 revenue = 525 and
similarly for each of the other 18 subsidiaries

I have tried various combinations of the sum, product and sumproduct command
on the consolidation page but keep encountering ref# errors

Can anybody help?

Thanks
Andrew

 

[Domenic]

Download and install the free add-in Morefunc.xll...

http://xcell05.free.fr/morefunc/english/index.htm

Then try...

=SUMPRODUCT(THREED('Sheet1:Sheet20'!B2),THREED('Sheet1:Sheet20'!B3))

Otherwise, without the add-in, try...

=SUMPRODUCT(N(INDIRECT("'"&$A$2:$A$21&"'!B2")),N(INDIRECT("'"&$A$2:$A$21&
"'!B3")))

....where A2:A21 contains the sheet names.

Hope this helps!

 

[Andrew Hodgson]

Many thanks, looks like the syntax I need
Regards
Andrew