sumproduct with threed

[russj]

Hi everyone, I'm trying to do something that appears rather complicated to me. I'm using an array function (sumproduct) but i need to use 3d references, which arent compatible with some array functions. So, I'm using threed to change a 3d array into a 2d array. This is all to work out how many time a text string appears in an array, within a certain month (the beginning of the month is listed in another, seperate array and the end in another seperate array). I may be overcomplicating things...
Can anyone take a look at the following formula and tell me why I always get a #REF error, regardless of where I put the brackets etc?

=SUMPRODUCT((THREED('December 2008:July 2008'!E2:E500))="Burford")*((THREED('December 2008:July 2008'!E2:E500))>=$L8)*((THREED('December 2008:July 2008'!E2:E500))<=$M8)

Thanks
Russj

 

[schielrn]

Welcome to the Board!

I believe your parenthesis are incorrect? Try:

=SUMPRODUCT((THREED('December 2008:July 2008'!E2:E500)="Burford")*(THREED('December 2008:July 2008'!E2:E500)>=$L8)*(THREED('December 2008:July 2008'!E2:E500)<=$M8))

Or maybe try this, but you should get the same results:

=SUMPRODUCT(--(THREED('December 2008:July 2008'!E2:E500)="Burford"),--(THREED('December 2008:July 2008'!E2:E500)>=$L8),--(THREED('December 2008:July 2008'!E2:E500)<=$M8))

At first glance that is one thing I noticed.

 

[russj]

Thanks Scheirl.

I think I have tried every possible combination of parenthesis's but none have worked - I've also tried that "--" thing, still it says #REF!
It works fine without THREED,, but I need THREED because I'm using multiple worksheets...

Driving me mad, have spent about 2 days on it with no success!

Russj

 

[schielrn]

Just to make sure, does July 2008 and December 2008 sheets exist? I assume they do, but just wanted to check? Maybe here is an error on those sheets?

 

[russj]

Yeah the sheets definately exist and there are worksheets for all the months in between, in the same format.

 

[schielrn]

Unfortunately I do not see anything wrong with the formula. I have some small sample data that I was testing with and it worked for me? There are other solutions to sum or count accross sheets without using threed, but that is not my strong point as I would maybe hope that Domenic comes accross this thread, because he is very good with those types of formulas. Here is something he did with sumif:

http://www.ozgrid.com/forum/showthread.php?t=27306

Maybe you can somehow adapt it. Sorry I couldn't be of more assistance.

 

[Domenic]

Make sure that the actual sheet names within the sheet tab are spelled correctly, and that there aren't any extra spaces before, after, and within the name itself.