I have two excel sheets: 1 for expenses that is monthly and another that is a yearly summer of expenses by expense type.
For the yearly expense sheet, I would like to sum every 12 months an put it in a cell, then the cell next to it is the next 12 months, and so on.
So to give an idea of how my sheet is set up:
Sheet titled 'Operating Expense' has cells B4:M4 that are Jan-Dec of that year for the monthly expenses.
On The sheet titled "sources & uses of funds", i would like to have call D28 be the sum of B4:M4, and then cell D29 the sum of N4:Y4 and so on until I stop dragging the formula across.
I tried the Sum(offset()) function but couldn't seem to figure it out.
Discard the SUM(OFFSET(...)) method and stick with summing a pair of INDEX function's defining a range on the 4th row. If will be faster and is a non-volatile function.
=SUM(INDEX('Operating Expense'!$4:$4,1,2+(ROW(1:1)-1)*12):INDEX('Operating Expense'!$4:$4,1,13+(ROW(1:1)-1)*12))
With that in D28, you can fill down for B4:M4, N4:Y4, etc.
How It Works:
Unlike other lookup functions (e.g. VLOOKUP function, HLOOKUP function, etc) the INDEX function can return a cell address reference that can be put to use directly withot resorting to additional overhead like the INDIRECT function. If fact, you can stitch two of them together with nothing more than a colon to create a usable cell range.
Syntax:
INDEX(<array>, <row_num>, <column_num>)
Example: (these all amount to the same thing)
=SUM(INDEX(A:A, 1, 1):INDEX(B:B, 5, 1))
=SUM(A1:INDEX(B:B, 5, 1))
=SUM(INDEX(A:A, 1, 1):B5)
=SUM(A1:B5)
Once we have control over the row and column of a cell range (aka array) we can manipulate them with a little math.
If you put =ROW(1:1) in any cell and fill down you will get 1, 2, 3, etc. This is how we sequence the stagger as we fill down.
You want the first set of 12 month to sum B4:M4 so the first part uses 4:4 for the <array>. This represents all of row 4. The <row_num> is 1 because there is only one row to consider.
You want to start the sum range at column B so that is numerically column 2. We don't want to add anything the first time but we want to add 12 as we fill down so the match is 2 + (ROW(1:1)-1) × 12 or 2+(ROW(1:1)-1)*12 for starting <column_num> of 2, 14, 26, etc.
Similarly, you want to end the sum range at column M so that is numerically column 13. We don't want to add anything the first time but we want to add 12 as we fill down so the match is 13 + (ROW(1:1)-1) × 12 or 13+(ROW(1:1)-1)*12 for ending <column_num> of 13, 25, 37, etc.
The resulting cell range(s) as you fill down will be B4:M4, N4:Y4, Z4:AK4, etc. Stuffing these into a SUM function will give you the respective totals.
As mentioned, this is a trait of INDEX. VLOOKUP, HLOOKUP, etc only return the values, not a usable cell reference.
