need help with a formula

[somethingsfishy]

Can someone please help with a formula

Basically I was using this formula:


=SUMPRODUCT((#REF!$R$10:$R$571>=A56)*(#REF!$R$10:$R$571<=B56)*(#REF!$S$10:$S$571))

of course where REF is was the then sheet name.

I use this formula to tally between this date and this date we finished this many. I have a date column on sheet 1 say and then I have a place that tally's it on sheet 2. The place that tallys it needs to be able to be variable that's why you have the >= A56 because in A56 I have the start date I want to look at and in B56 I have the end date I want to look at.

This formula worked perfectly for me until we decided sheet one was getting too long and we broke it up by due date. Each tab now has a date that these items are due.

I can't figure out how to make this formula work over the span of several worksheets.

I was trying to use something like this but it's not working either:


=SUMPRODUCT((INDIRECT("'"&$A$3:$A$18&"'!W$10:W$571">=$J$3)*(INDIRECT("'"&A3:A18&"'!W$10:W$571"<=$K$3)*(INDIRECT("'"&A3:A18&"'!X$10:X$571")))))

Any help would be greatly appreciated.

 

[dsaffo]

Did you try entering it as an array formula (after entering the formula press ctrl + shift + enter in stead of just enter). I am not sure if this will help or not, but hopefully it will make for an easy solution.

 

[somethingsfishy]

No unfortunately it did not work I'm getting the REF! error.

Anyone have any ideas?

Any help would be greatly appreciated.

 

[Mike Blackman]

Hi,

Hopefully this link will help;

http://www.exceltip.com/st/Summing_Across_Multiple_Sheets/1066.html

 

[somethingsfishy]

I can see how that could work but what if all my sheets have different names? They have names like OCT 6, OCT 13. They are due dates

 

[dsaffo]

Can you use a pivot table instead of the formula?