Forum Discussion
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.
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)
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.
