0

I'm trying to use a formula to do a sumif of a column of monthly data that changes depending on the current month. For example say I am entering this formula in column A of tab 2. Since it is October I want it to look at tab 1 and sumif the data in the October column. Next month when it is November, I want the same formula in column a of tab 2 to look at the next column over, November, in tab 1.

Improve this question

1 Answer 1

Reset to default
1

Lets say in tab 1 you have JAN to DEC in columns A to M and they have numbers you want to sum up to row 99.

You can get the current month with =MONTH(NOW()).

You can use the OFFSET command to offset into a range like this:

 =OFFSET($A$1:$M$99,0,0,99,1)

So this will chooses a part of the range A1:M99 that is 99 rows deep and one column wide starting from position 0,0 - the beginning of the first row of column A.

This will select a subset of the range starting from the second column (B):

 =OFFSET($A$1:$M$99,0,1,99,1)

So this formula will sum the contents of the column that corresponds with the current month:

 =SUM(OFFSET($A$1:$M$99,0,MONTH(NOW())-1,99,1)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .