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. 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 countif but did not work. Any suggestions ?

=COUNTIFS(D:D,"=2/6/2011<=2/12/2011",G:G,"High")

-
Thanks
Ram

And by the way it D to D and not grin !

Insert a column for the WEEKNUM function.

In this example, let's say Column E has the WEEKNUM for the dates of Column D:

=SUMPRODUCT(--(E:E=7),--(E:E<=9),--(G:G="High"))

Whe
7 is the WEEKNUM for 02/06/2011
9 is the WEEKNUM for 02/12/2011

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.

Thanks a lot wickedchew !! Am sorry for reposting earlier...I was desperately seeking some help. your formula works great..However when I try to use it in my sheet for some strange reason am getting value error ! I tried your formula in a seperate sample sheet and it works great..Problem comes when i try using it in my original sheet :(
this is the formula am using : Please let me know if you can find any errors in this !

=SUMPRODUCT(--(OFFSET($D$3,0,0,COUNTA($D:$D)-1,1)=$A$87),--(OFFSET($D$3,0,0,COUNTA($D:$D)-1,1)<=$A$88),--(OFFSET($G$3,0,0,COUNTA($G:$G)-1,1)=$A$89))
Assuming am using column D for dates and G for priorities and entering inputs in A87,A88 and A89. I verified using the color code and nothing seems to be wrong ! but when i hit enter it gives value error exclamation in the answer cell.

Attach the worksheet here as a zip file. Lemme' take a look at it.

Hi,

Please find the attached excel sheet..Please note i have removed all other columns since it was confidential data :) The 1st tab has the data on which i want to operate and at the bottom of the 1st tab i have tried to use the formula u mentioned but getting value error for say priority=high/critical. In "sheet2" tab I tried to use the same data columns and formula and it works fine !!!!! In the summary tab i have tried to the same formulas but in different sheet and tried to provide references to the data in 1st tab.

-Thanks
Ram

I see now you're getting an error with the formula. Don't leave any cells blank in Sheet 1 where the formula is checking at, it'll produce an error - I don't know why.

If the cell should be blank, input a minus sign (-) or a space instead of a null value.

Hi ,

Wow ! Awesome. You are right. I did not notice that. Thank you very much for the response.! One last Question, I was trying to use the NOW() function to eliminate the need to enter the start date and end date in the cells as inputs to the formula. Can that be done? Like say NOW()-7 and combine it with criteria and countifs? since I want to monitor the weekly status for high/low/critical etc.?

If you use NOW(), it will have a time variable. Use Today() instead.