We help IT Professionals succeed at work.

# How to use Offset function in excel where the starting cell is a variable

on
Hi,

I am using an offset function to sum a range of cells as follows

=SUM(OFFSET(Days!A13,0,8,21,1)) and this works fine

My issue is the starting cell can vary as this references the first Monday of the month. I can find the first Monday of the month in a range cells from A10 to A31 use an if statement and dropping the cell reference into another cell say A1 using the following CELL("address",Days!A13) so A1 now has Days!\$A\$13 in it.

When I try to do the following and replace the Days!A13 with CELL("contents",\$A\$1) in the Offset function so looks like this

=SUM(OFFSET(CELL("contents",\$A\$1),0,8,21,1))

Excel comes up with an error and wont even accept the formula.
Comment
Watch Question

## View Solution Only

Top Expert 2016
Commented:
Hi,

pls try

``````=SUM(OFFSET(INDIRECT(\$A\$1,1),0,8,21,1))
``````
Regards
Finance Analyst

Commented:
The syntax of the OFFSET function is, as you are no doubt aware:

=OFFSET(Reference,RowsOffset,ColumnsOffset,RowsHeight,ColumnsWidth)

With that you can use the columns or rows offset value to move to the correct row/column, leaving the Reference cell the same.

So your current formula starts at A13, moves 0 rows down and 8 columns across and returns a range that is 21 rows high and 1 column wide. I am suggesting that you stay with A13 as your reference but adjust the 0 or 8 to allow for the starting point to change.

You can include a MATCH function to find the correct date.

Thanks
Rob H
Finance Analyst

Commented:
So I think it would adjust to:

=SUM(OFFSET(A9,MATCH(Date,A10:A31,0),8,21,1))

In other words start at A9 and move down A10:A31 until the date is found, go across 8 columns and sum the range that is then 21 rows by 1 column from that point.

I have deliberately started at A9 because if the required date is in cell A10, the result of the MATCH will be 1 so the OFFSET will move down 1 from A9.

Thanks
Rob H

Commented:
Hi Rob,

I have adjusted your formula to work with my spreadsheet, but am having trouble with the match function.

The cells A10:A31 are filled by another formula with the date and formatted as the Day eg "Mon", "Tue"

So I have set this up but with no luck

=SUM(OFFSET(Days!A9,MATCH("Mon",TEXT(CELL("contents",Days!A10:A31),"ddd"),0),8,21,1)) as the TEXT function returns only the first day eg "Sun" for this month.

Can you please have a look at the match function and let me know if there is way to get this to work.

Thanks Mark
Finance Analyst

Commented:
As A10:A31 are only formatted as days, the true value in the cells is still the date so you don't need to convert to text.

Anyway, the MATCH function won't work in the way you tried. The lookup range has to be a range, by trying to convert that range to text within the formula you will create an error, difficult to explain at 11:30 at night but will try tomorrow. It might work if array entered, confirm entry will Ctrl & Shift & Enter.
Finance Analyst

Commented:
As you have spotted,  the TEXT function is only creating one entry and is using the first cell in the range A10:A31 so the MATCH is finding the relevant entry.

See attached file, I have tried to make a a mockup of your file with the info that I had. The list of dates in A10:A31 and values in column I (8 columns over) are just manual entries.

Enter first day of month in cell B2, from this G2 will calculate the first Monday using the following formula:

=IF(WEEKDAY(B2,1)=1,B2+1,IF(WEEKDAY(B2,1)=2,B2,CEILING(B2,7)+2))

In text, this says IF WEEKDAY of B2 = Sunday THEN use B2 + 1, ELSE IF WEEKDAY of B2 is Monday then use B2, ELSE round up B2 to following Saturday and add 2 to make the following Monday.

In H2 I have just put a link to G2 and formatted differently just to show that the formatting as "ddd" doesn't change the true value of the cell.

Your SUM(OFFSET(...)) formula is then in L5.

I have originally worked on using dates in Feb and as it happens the first Monday was only the second row in the list. So the OFFSET is then summing all but 1 of the 22 values in column I.

However, there is also a Jan sheet in the file and as you can see the 21 rows in the SUM goes beyond the bottom of the values in the I10:I31 range by three rows. Do you want this to happen? If the cells below the I10:I31 range will always be empty then it won't be a problem; if there could be values below that range we can adjust the 21 within the OFFSET function to allow for it.

Alternatively, if you are just trying to add up the values from column I where date in column A is greater than/equal to the First Monday then you can use the SUMIF function which is much simpler:

=SUMIF(\$A\$10:\$A\$31,">="&G2,\$I\$10:\$I\$31)

Shown in cell O5 of Feb sheet.

Hope this all helps. If not then can you upload a sample sheet to show what you need.

Thanks
Rob H
Days.xlsx
Finance Analyst

Commented:
So if you were going to accept the first solution, it would have been nice to know before I continued with my efforts.

Thanks