My workbook contain 13 sheets. One sheet for each month (January thru December) plus one sheet to be ale to run totals. I can easily use =Sum(January:December!B2) to calculate the sum of all B2 cells in sheets January thru December. However, i want the ability to select the range of sheets based off of cell A1 (which will have the desired beginning month) and cell A2 (which will have the desired ending month). Please Help !
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Dynamic Sum Across Sheets
James006
Well-known Member
- Joined
- Apr 4, 2009
- Messages
- 4,750
- Office Version
- 2016
- Platform
- Windows
Hello,
Make sure to type all input data as follows :
1. cell A1 : January
2. cell A2 : March
3. cell A3 : B2
which results in following Array Formula :
For an Array Formula ... instead of the standard Enter key ... you need to use simultaneously the three keys : Control Shift Enter ...
Hope this clarifies
Make sure to type all input data as follows :
1. cell A1 : January
2. cell A2 : March
3. cell A3 : B2
which results in following Array Formula :
Code:
[COLOR=#333333]=SUM(IF((MATCH(Sheets,Sheets,0)>=MATCH($A$1,Sheets,0))*(MATCH(Sheets,Sheets,0)<=MATCH($A$2,Sheets,0)),N(INDIRECT("'"&Sheets&"'!"&$A$3))))[/COLOR]For an Array Formula ... instead of the standard Enter key ... you need to use simultaneously the three keys : Control Shift Enter ...
Hope this clarifies
Upvote
0
Excel Facts
What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Similar threads
