Offset formula logic clarity

Question

I am trying to get year to desired month total of personal expenditure sub categories. After researching stackoverflow, I found a formula seemingly appropriate for my requirements. I found it shifting the desired area by one row down during formula evaluation. I modified the formula by hit and trial on adhoc basis which is giving the correct results. To me the initially chosen formula appeared quite appropriate. I have shown below the sample data sheet and the evaluation steps of the original and modified formula. Could someone explain particularly the offset portion as to why it was going wrong for the initially chosen formula and how the modification helped in solving the problem. Somehow I am not able to get conceptual clarity on this issue.

Sample Data files

Personal_Accounts evaluated with formula A

Personal_Accounts evaluated with modified formula

Answer 1

Offset works by specifying:

A cell from you which you will offset (A1 in this example) then specifying how many rows and columns to move from that position, and then how tall and wide to make the range.

The number of rows to move down: In this case the number of rows down is determined by Match(). Match() here will return the number of rows down in the range A1:A9 that the value SS can be found. The answer is 5. Offset now is looking at Range A1 + 5 rows: A6

The number of columns to move across: Here we move 1 column. No funny business. New range is B6

The number of rows to include in the range from that start point: Here COUNTIFS() is used to return the number of times SS is found in the range A2:A9. The answer is 3. So the range will start at B6 and include three rows down in the range. Essentially B6:B8.

Finally, the number of columns to include in the range: Here it's 7 since that's what you have in cell A13, so your range is now B6:H8

OFfseT() returns that range and Sum sums it up

You subtracted one from the results of MATCH() and correctly moved that formula to produce B5:H7. You could have also changed the search range in MATCH() to A2:A9, which would probably make more sense from a readability standpoint.

Lastly, your COUNTIFS() could just be COUNTIF() since you are not evaluating multiple conditions.

So if I had to write this from scratch, I would use:

=Sum(Offset(A1, Match(A2:A9, A12, 0), 1, Countif(A2:A9, A12), A13)

Which will get you the same correct answer, without any math on Match() results.

Answer 2

look at your basic offset formula definition.

Offest (REFERENCE CELL, HOW MANY ROWS TO MOVE FROM REFERENCE, HOW MANY COLUMNS TO MOVE FROM EFERENCE, HOW MANY ROWS TO RETURN, HOW MANY COLUMNS TO RETURN)

so if you set your reference cell to A1 and you want to return the result in A2, you need to move down 1 row from your reference cell.

OFFSET ($A$1,1,0,1,1)

Now if we look at the match portion of your equation, MATCH return what position the information is in. So if we want to find the match position of the information in A2 in a range going from A1:A100, Match is going to tell you that the information in A2 is in the 2nd position of the column. Or more precisely it returns a value of 2.

So now we need to tell offset how far down to reach the 2nd position. We dont actually want it to move down 2 rows to get to the second position since our reference point is A1 which is the first row. As a result we really want to go down 1 row to get to the second row. So you want 1 less from your match results which you correctly did by doing Match(...)-1

Answer 3

Offset has two main functions - either to move to cell (target) using specified number of rows and columns from the starting point, or to select range of specified number of rows and columns starting in the target cell. Your original formula has issue in this part

MATCH(A12;A1:A9;0)

matched cell is fifth therefore the offset moves 5 rows down ending in A6, because it starts in A1 + 5 rows. Then it moves 1 column to be in B6 and then creates range of 3 rows in total and 7 columns = B6:H8. So you need to deduct 1 from the result of the match function to end up in the right row. For better understanding imagine if the SS value was in the first row of the range A1:A9 (in A1) - then the offset would move from A1 one row down to A2 although you wouldnt want it to move at all.