Forum Discussion

Tyler Smith's avatar
Tyler Smith
Copper Contributor
Nov 06, 2018
Solved

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 ...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 09, 2018

    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)

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 24, 2019

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)))
)
Tyler Smith's avatar
Tyler Smith
Copper Contributor
Apr 24, 2019

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's avatar
    Tyler Smith
    Copper Contributor
    Apr 25, 2019

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 24, 2019

    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

Resources