Sumproduct function used between sheets

[JoelIgnall]

Hello,

At our work we have the need to perform the sumproduct Excel workshee
function between sheets (tabs). For example, we need to add up th
sums of products for the ranges c1:e1 and c2:e2 between sheets. In a
example I am working on, sheets 3 and 4 have numbers in this range.
The formula I am trying results in a #VALUE error, the formula is
"=SUMPRODUCT(Sheet3!C1:Sheet4!E1, Sheet3!C2:Sheet4!E2)".

Any suggestions would be greatly appreciated.

Thanks kindly,

Joe

 

[Frank Kabel]

Hi
some solutions:
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. e.g. you may use the following formula
=SUM((THREED('sheet1:sheet10'!A1:A1000)="condition
one")*(THREED('sheet12:sheet1
0'!B
1:B1000)))

enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column Bin which column A contains our criteria