How to supply Week values dynamically?
 

Hi ,

I am Newbie to excel formulaes. I was trying to write one for calculating the number of high and low priority issues from a column say "G" and for a particular week(displayed in column D which has date that issue was created). The problem in my case is that the data changes weekly and the sheet also contains data from 2-3 years.

I tried to use this formula which calculates the same for a specified week(feb 2nd week in this case). I want to make it generic. I tried using weeknum function along with sumproduct but that has 2 problems: 1) the values supplied in sumproduct formula again becomes hard coded when we consider a specific week.2) Since my sheet contains data from years; the february 2nd is week 6 for 2010 as well as 2011, so this inturn gives incorrect calculation considering last year week as well !! Any suggestions to overcome this problem ?

=COUNTIFS(D ,"=2/6/2011<=2/12/2011",G:G,"High")
or
=SUMPRODUCT(--(O:O=7),--(O:O<=8),--(G:G="High"))
assuming o column contains weeknum calculated for column D.

All I want to do is summarize only current or previous weekly data from the entire sheet and show the number of high/low priorities for issues that were created during that week. Is it possible to make it generic so that addition of more data cells in future to the same sheet will still display priority numbers for that week/previous week?

Thanks in advance !
-Ram

Your POST is answered he

http://www.excelbanter.com/showthrea...d=1#post964025