Combining COUNTIFS and OR function

[tatyanamarie]

Hi,

I'm trying to track actions between months with proposed closed dates, actual closed dates and tracking whether actions are overdue (see attached workbook). If actions are overdue, they are marked as 'Yes', however if they are overdue and still open (ie. not completed) at the end of the month they were supposed to be closed in, I need them to be tracked into the next month.

Eg. If I was supposed to get my hair cut in March but forgot to, then I need this action number to be added to the total number of actions due for closure in April. I need to be able to track the action whether it is open or closed, as long as it is overdue and outside of the month it was due.

My current thinking is a formula that combines =COUNTIFS('Tracking Register'!C:C,">=01/03/2013",'Tracking Register'!C:C,"<01/04/2013",'Tracking Register'!G:G,"=Yes",'Tracking Register'!D:D,"=Open") and something about being raised for closure and due in that month, but date completed (column F) is outside that month. I need to combine these so that the action does not disappear from the number of actions due for closure when it is set to 'closed'.

Many thanks in advance!!

sample book.xls

[dilipandey]

HI tatyanmarie,

I believe you need the result in g2 cell of sheet "analysis".
Can you enter you expected results manually after removing the formula and explain how you arrived at that result ? Thanks.


Regards,
DILIPandey
<click on below * if this helps>

[tatyanamarie]

Hi dilipandey,

Sorry, just noticed an error in the formulae. Please see updated workbook. You'll notice that G2 on the analysis tab currently says 1 action rolling over to the next month. So the code in C3 would then be =COUNTIFS('Tracking Register'!A:A,">=1/04/2013",'Tracking Register'!A:A,"<01/05/2013") PLUS the number of actions rolling over into the next month. However, with the current code in G2, once you close the overdue action it becomes zero (where in this case it needs to stay 1).

So I need it to count if the action is open and overdue into the next month, but still be counted once it is closed.

[dilipandey]

Hi tatyanamarie,

You'll notice that G2 on the analysis tab currently says 1 action rolling over to the next month.

I am still seeing zero there

As I mentioned, to help you better, I just need the FINAL results from your side in those cell along with respective reasoning... for example :-

cell G2 is 5 because "----"
cell B2 is 6 because "____"
cell "xyz" is "abc" because "____" .. that's it


Regards,
DILIPandey
<click on below * if this helps>