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
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