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.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: right"]current week[/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Remaining Amt[/TD]
[TD="align: right"]Weeknum[/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"]18
[/TD]
[TD="align: center"]19
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]21
[/TD]
[TD="align: center"]22
[/TD]
[TD="align: center"]23
[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]# avail by Wk
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
-- 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,