I need an array that looks across multiple sheets and gives a sum product
based on an account number. I have tried the following formula that should
work, but returns a #Ref.

The formula I am trying is
=SUMPRODUCT(--('ME:[Budget]Budget'!B484:B510=Total!B484),('ME:[Budget]Budget'!C484:C510))

The account number I need to match is in Total b484, and I need to add the
numbers in c484:c510 that matches this account number asross the sheets ME
through Budget.

Any suggestions?

Excel is poor when it comes to any 3D functions, one way of getting this
done is to put the names of all sheets in a list, so assume you have 8
sheets put the names of those sheets in the same order they occur in the
workbook in let's say H1:H8, then you can use this formula


=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H8&"'!B484:B510" ),Total!B484,INDIRECT("'"&H1:H8&"'!C484:C510")))


--


Regards,


Peo Sjoblom




"D" wrote in message
...
I need an array that looks across multiple sheets and gives a sum product
based on an account number. I have tried the following formula that
should
work, but returns a #Ref.

The formula I am trying is
=SUMPRODUCT(--('ME:[Budget]Budget'!B484:B510=Total!B484),('ME:[Budget]Budget'!C484:C510))

The account number I need to match is in Total b484, and I need to add the
numbers in c484:c510 that matches this account number asross the sheets ME
through Budget.

Any suggestions?

THanks, I simply added sum products across all needed sheets, it is long but
at least it works consistantly.

Are any of these 3d deficencies addressed in excell 2007?

"Peo Sjoblom" wrote:

Excel is poor when it comes to any 3D functions, one way of getting this
done is to put the names of all sheets in a list, so assume you have 8
sheets put the names of those sheets in the same order they occur in the
workbook in let's say H1:H8, then you can use this formula


=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H8&"'!B484:B510" ),Total!B484,INDIRECT("'"&H1:H8&"'!C484:C510")))


--


Regards,


Peo Sjoblom




"D" wrote in message
...
I need an array that looks across multiple sheets and gives a sum product
based on an account number. I have tried the following formula that
should
work, but returns a #Ref.

The formula I am trying is
=SUMPRODUCT(--('ME:[Budget]Budget'!B484:B510=Total!B484),('ME:[Budget]Budget'!C484:C510))

The account number I need to match is in Total b484, and I need to add the
numbers in c484:c510 that matches this account number asross the sheets ME
through Budget.

Any suggestions?

You did the best solution although a SUMIF would be faster.
The 3D formula I posted have some drawbacks, it uses INDIRECT which makes
the formula volatile, that means for instance if you just open the workbook
and look at data you will still be prompted to save it since volatile
function always recalculate even if precedents have not changed
Sop the fastest way for you would be to use SUMIF on each page, and then
just sum those cells with a 3D SUM function. The second fastest would be to
use SUMPRODUCT like you did

Excel 2007 is as limited as earlier version in this regard


--


Regards,


Peo Sjoblom



"D" wrote in message
...
THanks, I simply added sum products across all needed sheets, it is long
but
at least it works consistantly.

Are any of these 3d deficencies addressed in excell 2007?

"Peo Sjoblom" wrote:

Excel is poor when it comes to any 3D functions, one way of getting this
done is to put the names of all sheets in a list, so assume you have 8
sheets put the names of those sheets in the same order they occur in the
workbook in let's say H1:H8, then you can use this formula


=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H8&"'!B484:B510" ),Total!B484,INDIRECT("'"&H1:H8&"'!C484:C510")))


--


Regards,


Peo Sjoblom




"D" wrote in message
...
I need an array that looks across multiple sheets and gives a sum
product
based on an account number. I have tried the following formula that
should
work, but returns a #Ref.

The formula I am trying is
=SUMPRODUCT(--('ME:[Budget]Budget'!B484:B510=Total!B484),('ME:[Budget]Budget'!C484:C510))

The account number I need to match is in Total b484, and I need to add
the
numbers in c484:c510 that matches this account number asross the sheets
ME
through Budget.

Any suggestions?