Formula HELP!!

I believe this is exactly what I was looking for. I was way over thinking how to write it. Thank you very much for the help!!

Tyler Smith , yes, in initial formula was "blank or, if date, in later month", I forgot to remove checking on blank. Check if text here or not also could be removed, only "within the month"

=SUMPRODUCT(
   ISNUMBER(SEARCH("Plan",$R$3:$BA$3))*
   ($R$4:$BA$34>=(EOMONTH(CQ4,-1)+1))*
   ($R$4:$BA$34<=EOMONTH(CQ4,0))*
   (OFFSET($R$4:$BA$34,0,1)>=(EOMONTH(CQ4,-1)+1))*
   (OFFSET($R$4:$BA$34,0,1)<=EOMONTH(CQ4,0))
)

I didn't check the source data if result is correct, please inform if something is wrong

This is almost what I am looking for, the only thing is that it is counting the items as "completed" before the actual column has been entered. Once the actual column had been entered it does work correctly.

So I would like it to look at the "Plan" Column and if it is due in that month, then look at the "Actual" column to see if that task was completed in that month. But like I said above I need it to not count anything in the "Plan" Column until a date has been entered in the "Actual" column. I hope this clears up what I'm trying to do.

I apologize for the confusion. 

SergeiBaklan 

Tyler Smith ,

Do you mean to calculate number of plans completed at the same month as planned? When it's only to adjust a bit second part of the previous formula

=SUMPRODUCT(
   ISNUMBER(SEARCH("Plan",$R$3:$BA$3))*
   ($R$4:$BA$34>=(EOMONTH(CQ4,-1)+1))*
   ($R$4:$BA$34<=EOMONTH(CQ4,0))*
   (ISBLANK(OFFSET($R$4:$BA$34,0,1))+ISNUMBER(OFFSET($R$4:$BA$34,0,1))*(OFFSET($R$4:$BA$34,0,1)>=(EOMONTH(CQ4,-1)+1)))*
   (ISBLANK(OFFSET($R$4:$BA$34,0,1))+ISNUMBER(OFFSET($R$4:$BA$34,0,1))*(OFFSET($R$4:$BA$34,0,1)<=EOMONTH(CQ4,0)))
)

I figured it would be easier for everyone instead of having to re explain the entire situation.Twifoo 

Hello,

I know I am reaching back a while, but I figured since I already have this thread and my problem has to do with the same problem i would continue it on. So my problem now is I need to Compare both of my columns (Plan , Actual), if the Plan has a date within a certain month, (lets say march), then I need the formula to look at the actual and see if it was also completed in the same month. 

Thank you for the help!

SergeiBaklan 

Okay I believer you have finally got it! Thank you so much for all the help, I would of never been able to figure out that formula on my own. 

Sorry i had a typo, it should of said, "Shouldn't be counted if N/A". So if there is an N/A in the actual column it shouldn't be counted towards the due not completed formula. As of now it seems that it is also counting any N/A in the actual column. Sorry for this being such a pain!

Hi Tyler,

So, if blank and "N/A" both are considered as not completed and any earlier date as completed the update as in my previous post shall work

=SUMPRODUCT(ISNUMBER(SEARCH("Plan",$V$3:$W$3))*($V$4:$W$34>=(EOMONTH(CH17,-1)+1))*($V$4:$W$34<=EOMONTH(CH17,0))*(NOT(ISNUMBER(OFFSET($V$4:$W$34,0,1)))+(OFFSET($V$4:$W$34,0,1)>EOMONTH(CH17,0))))

I just checked what you were saying and everything does work good, but the problem I noticed that if it were to be done early before that month it will still count as due not completed. I should of been more clear on my statement I apologize for the confusion. Also any cells that have an "N/A" in them should be counted either. Sorry again for the confusion and not giving all the information. 

Hi Wyn,

They are related, formula compares Plan vs Actual for each cell. Another story the requirement was taken literally

I need a formula to find out what was due in a certain month but not completed in that month.

Other words if task is completed earlier than in planned month it is also calculated. If the requirement is

not completed or completed later than in due month

when first OFFSET

(OFFSET($R$4:$BA$34,0,1)<EOMONTH(CQ4,-1))

could be changed on

NOT(ISNUMBER(OFFSET($R$4:$BA$34,0,1)))

ISNUMBER here is to consider both blank and text cells ("N/A") as not completed

Hi -  Nice work SergeiBaklan but just to clarify Tyler Smith,  if an item is completed in October 2017 and planned for October 2018  did you  want that planned October 2018 item to be included in the planned but not completed count?

Sergei's formula is giving the result of all items planned in October v all items completed in October regardless of whether they are related to each other.  

I'm not sure whether the "Items due not completed" result is meaningful