• 463K Members
• 10.4K Online
• 560K Conversations
SOLVED

Contributor

# Formula HELP!!

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

# RE: Formula HELP!!

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

# Re: RE: Formula HELP!!

Hi Tyler

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

Take a look at the attached

# Re: RE: Formula HELP!!

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.

# Re: RE: Formula HELP!!

Anyone else able to suggest a solution to Tyler?

# Re: RE: Formula HELP!!

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

# Re: RE: Formula HELP!!

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!

# Re: RE: Formula HELP!!

Tyler, you are welcome

# Re: RE: Formula HELP!!

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

# Re: RE: Formula HELP!!

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

# Re: RE: Formula HELP!!

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.

# Re: RE: Formula HELP!!

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

# Re: RE: Formula HELP!!

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!

Solution

# Re: RE: Formula HELP!!

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)

# Re: RE: Formula HELP!!

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.

# Re: RE: Formula HELP!!

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!

# Re: RE: Formula HELP!!

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

# Re: RE: Formula HELP!!

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

# Re: RE: Formula HELP!!

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

# Re: RE: Formula HELP!!

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.

# Re: RE: Formula 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

# Re: RE: 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!!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies