SOLVED

Formula HELP!!

Copper Contributor

I need help with a formula I have been trying to figure out for 3 days.

 

If you look at the example I have a "Plan" column that gets auto populated by certain criteria. This is our target date to have something done. The actual column is of course the actual date it was completed. 

 

I need a formula to find out what was due in a certain month but not completed in that month. I know this is probably really confusing so please let me know if you need more clarification. I have hidden a majority of the Document to keep it less overwhelming cause I know it can be for me at times.I have no idea how to even know what function to use due to having so many criteria.

21 Replies
Does anyone have any idea of how to do this? It's driving me crazy!

Hi Tyler

 

My approach would be to add some helper columns for Year and Month

 

Take a look at the attached

This would work for a small batch of data but with 36 columns and ever expanding rows I would be worried about something getting missed along the way and giving wrong data.

Anyone else able to suggest a solution to Tyler?

Perhaps

=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))+(OFFSET($R$4:$BA$34,0,1)>EOMONTH(CQ4,0))))

 

Wow thank you so much!! I would of never even thought of going that route with the formula but after testing it is working perfect! Thank you again. This is the best forum ever!

Tyler, you are welcome

Hi -  Nice work @Sergei Baklan 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 

 

image.png

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

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 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))))

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!

best response confirmed by Tyler Smith (Copper Contributor)
Solution

Okay, when

=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,0))))

We count blank cell and don't count cells with any text ("N/A" in particular)

 

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. 

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!

 

@Sergei Baklan 

You have terminated this conversation. Thus, I advise you to start anew. Otherwise, this thread might go on forever!

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

@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)))
)

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. 

@Sergei Baklan 

1 best response

Accepted Solutions
best response confirmed by Tyler Smith (Copper Contributor)
Solution

Okay, when

=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,0))))

We count blank cell and don't count cells with any text ("N/A" in particular)

 

View solution in original post