Problem with THREED

[trone77]

I am having issues using THREED. When I enter the following formula it works just fine:

=SUMPRODUCT(--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$C$8:$C$38)=1),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$D$8:$D$38)=0),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$E$8:$E$38)=2))

However when I add to that formula to include another arguement it doesnt work:

=SUMPRODUCT(--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$B$8:$B$38)="Sun"),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$C$8:$C$38)=1),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$D$8:$D$38)=0),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$E$8:$E$38)=2))

Is there a limit to how many argument you can include with THREED?

 

[Domenic]

Sorry, somewhat unclear... Are you saying that Column B in 'All Months.xlsx' contains formulas? In any case, if for example B10 in Sheet 'Jan 00' of 'All Months.xlsx' has a value equal to 'Sun', then the following should return TRUE regardless of whether the value is derived from a formula...

='[All Months.xlsx]Jan 00'!B10="Sun"

 

[trone77]

Yeah All Months did contain a formula, but to simplify things, I just went ahead and manually changed the contents of B to all text. Now when I use Threed with the formula below with "Sun" it always returns 0.

=SUMPRODUCT(--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$B$8:$B$38)="Sun"),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$C$8:$C$38)=4),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$D$8:$D$38)=8),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$E$8:$E$38)=8))


However if I remove the '--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$B$8:$B$38)="Sun")'...so just numbers and no text, it returns the correct answer.

=SUMPRODUCT(--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$C$8:$C$38)=4),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$D$8:$D$38)=8),--(THREED('[All Months.xlsx]Jan 00:Dec 11'!$E$8:$E$38)=8))