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)

     

Tyler Smith's avatar
Tyler Smith
Copper Contributor
Nov 06, 2018
Does anyone have any idea of how to do this? It's driving me crazy!
    • Tyler Smith's avatar
      Tyler Smith
      Copper Contributor
      Nov 07, 2018

      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.

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Nov 08, 2018
        Anyone else able to suggest a solution to Tyler?

Resources