Quote:
Originally Posted by justkar4u
Hi,
Thanks for the quick reply. Your solution works correctly if the excel sheet has only 1 years data. In my case the sheet has data for years 2009,2010,2011 etc. So the weeknum returns 6 for 2nd feb for both 2010 and 2011. BUT I want to summarize only current weekly data from the entire sheet. And one more problem with this approach is that you had suggested hard coding 7 and 9 for 2nd week of feb. 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?
|
Yes! That is possible.
In this picture, cell
E5 contains this formula:
=SUMPRODUCT(--(OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)=$E$1),--(OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)<=$E$2),--(OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)=$E$3))
This would make your search criteria dynamic.
NOTE:
You need not to do a double post.