Sum offset match

[nicky20]

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

 

[MixedUpExcel]

Hi Nicky,

Try this:

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

Thanks.

Simon

<tbody>
</tbody>