# need help with a formula

#### somethingsfishy

##### New Member

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.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### dsaffo

##### Board Regular
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

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

Anyone have any ideas?

Any help would be greatly appreciated.

#### somethingsfishy

##### New Member
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

##### Board Regular
Can you use a pivot table instead of the formula?

Replies
3
Views
325
Replies
5
Views
160
Replies
3
Views
117
Replies
6
Views
75
Replies
4
Views
125

1,191,607
Messages
5,987,635
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back