Hi Folks!

Working on a response to a post that requires 3D referencing across multiple
sheets. This could be done using an intermediate calculation and then a
simple SUM for the final result. I'm trying to eliminate that intermediate
calc.

Column A = dates
Column B = item
Column C = numeric values

This formula returns #VALUE! but when I step through it using F9 it
evaluates to the proper result:

=SUMPRODUCT(SUM((MONTH(INDIRECT("'"&H1:H2&"'!a1")) =1)*(INDIRECT("'"&H1:H2&"'!b1")="car")*(INDIRECT(" '"&H1:H2&"'!c1"))))

H1:H2 are the sheet names

Here are the eval steps:

=SUMPRODUCT(SUM((MONTH({38353;38353})=1)*({"car";" car"}="car")*({100;100})))

=SUMPRODUCT(SUM(({TRUE;TRUE})*({TRUE;TRUE})*({100; 100})))

=SUMPRODUCT(SUM({100;100}))

=SUMPRODUCT(200)

200

Why is this formula returning #VALUE! ?

Thanks
Biff

You run up against the so-called dereferencing problem whose solution
requires a second round of evaluation. N() and T() are functions which
are frequently invoked to effect that evaluation:

=SUMPRODUCT(--(MONTH(N(INDIRECT("'"&H1:H2&"'!A1")))=1),--(T(INDIRECT("'"&H1:H2&"'!B1"))="car"),--(N(INDIRECT("'"&H1:H2&"'!C1"))))

BTW, an alternative is to invoke a formula with Longre's THREED...

=SUMPRODUCT(--(MONTH(THREED(Sheet2:Sheet3!A1))=1),--(THREED(Sheet2:Sheet3!B1)="car"),THREED(Sheet2:She et3!C1))


Biff wrote:
Hi Folks!

Working on a response to a post that requires 3D referencing across multiple
sheets. This could be done using an intermediate calculation and then a
simple SUM for the final result. I'm trying to eliminate that intermediate
calc.

Column A = dates
Column B = item
Column C = numeric values

This formula returns #VALUE! but when I step through it using F9 it
evaluates to the proper result:

=SUMPRODUCT(SUM((MONTH(INDIRECT("'"&H1:H2&"'!a1")) =1)*(INDIRECT("'"&H1:H2&"'!b1")="car")*(INDIRECT(" '"&H1:H2&"'!c1"))))

H1:H2 are the sheet names

Here are the eval steps:

=SUMPRODUCT(SUM((MONTH({38353;38353})=1)*({"car";" car"}="car")*({100;100})))

=SUMPRODUCT(SUM(({TRUE;TRUE})*({TRUE;TRUE})*({100; 100})))

=SUMPRODUCT(SUM({100;100}))

=SUMPRODUCT(200)

200

Why is this formula returning #VALUE! ?

Thanks
Biff

Thank you, Aladin!

That worked!

Biff

"Aladin Akyurek" wrote in message
...
You run up against the so-called dereferencing problem whose solution
requires a second round of evaluation. N() and T() are functions which are
frequently invoked to effect that evaluation:

=SUMPRODUCT(--(MONTH(N(INDIRECT("'"&H1:H2&"'!A1")))=1),--(T(INDIRECT("'"&H1:H2&"'!B1"))="car"),--(N(INDIRECT("'"&H1:H2&"'!C1"))))

BTW, an alternative is to invoke a formula with Longre's THREED...

=SUMPRODUCT(--(MONTH(THREED(Sheet2:Sheet3!A1))=1),--(THREED(Sheet2:Sheet3!B1)="car"),THREED(Sheet2:She et3!C1))


Biff wrote:
Hi Folks!

Working on a response to a post that requires 3D referencing across
multiple sheets. This could be done using an intermediate calculation and
then a simple SUM for the final result. I'm trying to eliminate that
intermediate calc.

Column A = dates
Column B = item
Column C = numeric values

This formula returns #VALUE! but when I step through it using F9 it
evaluates to the proper result:

=SUMPRODUCT(SUM((MONTH(INDIRECT("'"&H1:H2&"'!a1")) =1)*(INDIRECT("'"&H1:H2&"'!b1")="car")*(INDIRECT(" '"&H1:H2&"'!c1"))))

H1:H2 are the sheet names

Here are the eval steps:

=SUMPRODUCT(SUM((MONTH({38353;38353})=1)*({"car";" car"}="car")*({100;100})))

=SUMPRODUCT(SUM(({TRUE;TRUE})*({TRUE;TRUE})*({100; 100})))

=SUMPRODUCT(SUM({100;100}))

=SUMPRODUCT(200)

200

Why is this formula returning #VALUE! ?

Thanks
Biff