Using WeekNum as a variable to total a sum range with a criteria range

[StarvinDan]

Thanks in advance for helping out...
Excel 2013 / Win7

I would like to update the criteria in a formula which contains a criteria range based upon the value of the weeknum. The range of cells decreases as each week passes. I have one cell which is the criteria =weeknum(today()), a criteria_range which contains weeknum() for each of the 7 days in each week over a 3 month period and a sum_range which contains the numbers I would like to total. Sum range = N20:CZ:20, criteria range = N19:CZ19 and criteria ">=M16" which is weeknum(today()) in cell M16

I've tried if, sumif and sumifs and cannot get this to work - any help would be greatly appreciated

In the table below I show a collapsed version of the cell range for several weeks, the entries for # available per week by weeknum and the current total under remaining amt.

current week	19
Remaining Amt	Weeknum	17	18	19	20	21	22	23
13	# avail by Wk	2	2	2	4	2	3	2

<tbody>
</tbody>

-- Dan

 

[RickXL]

Hi,

Try this:

=SUMIF(N19:CZ19,">"&M16,N20:CZ20)

Regards,

 

[StarvinDan]

Thanks RickXL, that did the job! Great to learn something new.

Dan

 

[RickXL]

No problem.

If my experience is anything to go by you never stop learning new stuff about Excel.

Regards,