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?

 

[Dave Patton]

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

What does this mean? Did you receive an error message? What message?

Does the formula work within the file All Months.xlsx?

 

[Domenic]

When you say that it doesn't work, what do you mean exactly? Does it return an error? If so, which one? Or does it return zero? Or does it return something else? Does Column B in fact contain text values as your formula suggests? Or does it actually contain true date values formatted as "ddd"? If the latter, try...

=SUMPRODUCT(--(WEEKDAY(THREED('[All Months.xlsx]Jan 00:Dec 11'!$B$8:$B$38),2)=7),--(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))

 

[trone77]

Well it give a return value of zero where it should return 6.

 

[trone77]

@Domenic.....column B does contain text, the days of the week....

 

[Domenic]

Check to make sure that the dates in Column B do not contain extra spaces...

 

[trone77]

No extra spaces

 

[Domenic]

Find a row where all four columns meet the criteria. Let's say that all four columns in Row 10 meet the criteria. The following formula should return TRUE...

=B10="Sun"

Does it return TRUE? Of course, substitute B10 for the actual cell.

 

[trone77]

It returns true

 

[trone77]

Ok i just remembered something. When I used =b10="Sun" in the Monthly Tally file "Sun" is actually text and that returns true, but from the All Months file its a formula referencing the cell b10 it returned false, and thats just a simple 'is =b10'