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

StarvinDan

New Member
Joined
Oct 27, 2014
Messages
2
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 week19
Remaining AmtWeeknum17
18
19
20
21
22
23
13# avail by Wk
2
2
2
4
2
3
2

<tbody>
</tbody>
-- Dan
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
No problem.

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

Regards,
 

Watch MrExcel Video

Forum statistics

Threads
1,099,914
Messages
5,471,476
Members
406,764
Latest member
ExcelMaker007

This Week's Hot Topics

Top