# Sum offset match

#### nicky20

##### New Member
Hi,

I have a worksheet and in that sheet cell A1 says the month, B1 says the week ending date and C1 tells me how many weeks there are in a month.

Row 2 I have the week ending dates for the year
Row 3 is the data

At the end of the dates in row two I have this formula:

=SUM(OFFSET(A3,0,0,1,MATCH(B1,A2:W2)))

Which sums all the range up until a specified date, however, I need the formula to only add a number of cells before this date - this number is variable as it changes each month.depending on what is in cell C1. I tried to add this into my formula but to no avail, can some provide me with the correct formula and explain how you got to it?

Many thanks!

Nicky

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Nicky,

Try this:

Code:
``=SUM(OFFSET(A3,0,MATCH(B1,\$A\$2:\$W\$2,0)-1,1,-C1))``

Thanks.

Simon

<tbody>
</tbody>

Replies
5
Views
127
Replies
1
Views
262
Replies
4
Views
214
Replies
2
Views
67
Replies
3
Views
150

1,196,181
Messages
6,013,909
Members
441,794
Latest member
Deepakjaat31

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back