local range names within Threed function?

[DEllis81]

I am attempting to use conditionally sum across multiple tabs using the "threed' function. What I have found - using the threed function with cell references appears to work.

For example this formula proves successful:
=SUMPRODUCT((THREED(CropB:CropA!$D$5:$D$20)=InputSetup!$A$7)*(THREED(CropB:CropA!$M$5:$M$20))).

However when I substitute cell references with local range names, I get a #REF error
=SUMPRODUCT((THREED(CropB:CropA!InputName)=InputSetup!$A$7)*(THREED(CropB:CropA!PurUnits)))

I have validated the two range names are defined locally on the CropA & CropB tabs are are consistenly sized. Are local range names not allowed within the threed function (or similar context), and if not, is there a work around? I really need differing range dimensions for each tab (but equal rows) within a tab for the two named ranges.

Thanks in advance for guidance!
daryl

 

[Aladin Akyurek]

I don't think that's possible. The reason might be that a defined name includes sheet name some way. That would conflict with the sheet refs THREED needs.

 

[AlphaFrog]

One workaround might be to use multiple SUMIFs...

=SUMIF(CropA!InputName, InputSetup!$A$7, CropA!PurUnits)+ 
  SUMIF(CropB!InputName, InputSetup!$A$7, CropB!PurUnits)

With this method, you could have unique named ranges on each sheet if you wanted.