SOLVED

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

# 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

Highlighted

# 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!!