Excel formula to return the sum of a range of cells on a separate worksheet

[mlm1980]

I'm trying to write a formula that returns the total purchase or sales of a share during a specific period. Each share is on it's own tab within the workbook. The formula I currently have for purchases is:
=SUMIFS(INDIRECT(A7&"!"&B10:B52),INDIRECT(A7&"!"&B10:B52),"<0",INDIRECT(A7&"!"&B10:B52),INDIRECT(A7&"!"&B10:B52),"<="&B2,INDIRECT(A7&"!"&B10:B52),INDIRECT(A7&"!"&B10:B52),">="&B2)
If I remove the Indirect and replace it with the tab reference then the formula works, however I really need this to work dynamically.

 

[Toadstool]

Hi mlm1980,

I'm a bit puzzled by the "<0" part as it looks like the target sheet B10:B52 may be dates. Given that is amended then is this what you want?

=SUMIFS(INDIRECT(A7&"!B10:B52"),INDIRECT(A7&"!B10:B52"),"<0",INDIRECT(A7&"!B10:B52"),"<="&B2,INDIRECT(A7&"!B10:B52"),">="&B2)

 

[mlm1980]

Hi mlm1980,

I'm a bit puzzled by the "<0" part as it looks like the target sheet B10:B52 may be dates. Given that is amended then is this what you want?

=SUMIFS(INDIRECT(A7&"!B10:B52"),INDIRECT(A7&"!B10:B52"),"<0",INDIRECT(A7&"!B10:B52"),"<="&B2,INDIRECT(A7&"!B10:B52"),">="&B2)

The <0 is to determine whether the amount is less than 0 on this case. Formula worked once I made the cell references (except A7) absolute. Thank you!

 

[Toadstool]

The <0 is to determine whether the amount is less than 0 on this case. Formula worked once I made the cell references (except A7) absolute. Thank you!

You're welcome.