B
Biff
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
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