SOLVED

Formula HELP!!

%3CLINGO-SUB%20id%3D%22lingo-sub-283018%22%20slang%3D%22en-US%22%3EFormula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283018%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20help%20with%20a%20formula%20I%20have%20been%20trying%20to%20figure%20out%20for%203%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20look%20at%20the%20example%20I%20have%20a%20%22Plan%22%20column%20that%20gets%20auto%20populated%20by%20certain%20criteria.%20This%20is%20our%20target%20date%20to%20have%20something%20done.%20The%20actual%20column%20is%20of%20course%20the%20actual%20date%20it%20was%20completed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20to%20find%20out%20what%20was%20due%20in%20a%20certain%20month%20but%20not%20completed%20in%20that%20month.%20I%20know%20this%20is%20probably%20really%20confusing%20so%20please%20let%20me%20know%20if%20you%20need%20more%20clarification.%20I%20have%20hidden%20a%20majority%20of%20the%20Document%20to%20keep%20it%20less%20overwhelming%20cause%20I%20know%20it%20can%20be%20for%20me%20at%20times.I%20have%20no%20idea%20how%20to%20even%26nbsp%3Bknow%20what%20function%20to%20use%20due%20to%20having%20so%20many%20criteria.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-283018%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284620%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284620%22%20slang%3D%22en-US%22%3E%3CP%3EOkay%20I%20believer%20you%20have%20finally%20got%20it!%20Thank%20you%20so%20much%20for%20all%20the%20help%2C%20I%20would%20of%20never%20been%20able%20to%20figure%20out%20that%20formula%20on%20my%20own.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284615%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284615%22%20slang%3D%22en-US%22%3E%3CP%3EOkay%2C%20when%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(ISNUMBER(SEARCH(%22Plan%22%2C%24R%243%3A%24BA%243))*(%24R%244%3A%24BA%2434%26gt%3B%3D(EOMONTH(CQ4%2C-1)%2B1))*(%24R%244%3A%24BA%2434%26lt%3B%3DEOMONTH(CQ4%2C0))*(ISBLANK(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1))%2BISNUMBER(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1))*(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)%26gt%3BEOMONTH(CQ4%2C0))))%3C%2FPRE%3E%0A%3CP%3EWe%20count%20blank%20cell%20and%20don't%20count%20cells%20with%20any%20text%20(%22N%2FA%22%20in%20particular)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284586%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284586%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20i%20had%20a%20typo%2C%20it%20should%20of%20said%2C%20%22Shouldn't%20be%20counted%20if%20N%2FA%22.%20So%20if%20there%20is%20an%20N%2FA%20in%20the%20actual%20column%20it%20shouldn't%20be%20counted%20towards%20the%20due%20not%20completed%20formula.%20As%20of%20now%20it%20seems%20that%20it%20is%20also%20counting%20any%20N%2FA%20in%20the%20actual%20column.%20Sorry%20for%20this%20being%20such%20a%20pain!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284565%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284565%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Tyler%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20if%20blank%20and%20%22N%2FA%22%20both%20are%20considered%20as%20not%20completed%20and%20any%20earlier%20date%20as%20completed%20the%20update%20as%20in%20my%20previous%20post%20shall%20work%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(ISNUMBER(SEARCH(%22Plan%22%2C%24V%243%3A%24W%243))*(%24V%244%3A%24W%2434%26gt%3B%3D(EOMONTH(CH17%2C-1)%2B1))*(%24V%244%3A%24W%2434%26lt%3B%3DEOMONTH(CH17%2C0))*(NOT(ISNUMBER(OFFSET(%24V%244%3A%24W%2434%2C0%2C1)))%2B(OFFSET(%24V%244%3A%24W%2434%2C0%2C1)%26gt%3BEOMONTH(CH17%2C0))))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284560%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284560%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20checked%20what%20you%20were%20saying%20and%20everything%20does%20work%20good%2C%20but%20the%20problem%20I%20noticed%20that%20if%20it%20were%20to%20be%20done%20early%20before%20that%20month%20it%20will%20still%20count%20as%20due%20not%20completed.%20I%20should%20of%20been%20more%20clear%20on%20my%20statement%20I%20apologize%20for%20the%20confusion.%20Also%20any%20cells%20that%20have%20an%20%22N%2FA%22%20in%20them%20should%20be%20counted%20either.%20Sorry%20again%20for%20the%20confusion%20and%20not%20giving%20all%20the%20information.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284464%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284464%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Wyn%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThey%20are%20related%2C%20formula%20compares%20Plan%20vs%20Actual%20for%20each%20cell.%20Another%20story%20the%20requirement%20was%20taken%20literally%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%0A%3CP%3E%3CSPAN%3EI%20need%20a%20formula%20to%20find%20out%20what%20was%20due%20in%20a%20certain%20month%20but%20not%20completed%20in%20that%20month.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3EOther%20words%20if%20task%20is%20completed%20earlier%20than%20in%20planned%20month%20it%20is%20also%20calculated.%20If%20the%20requirement%20is%3C%2FP%3E%0A%3CP%3E%3CEM%3Enot%20completed%20or%20completed%20later%20than%20in%20due%20month%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3Ewhen%20first%20OFFSET%3C%2FP%3E%0A%3CPRE%3E(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)%26lt%3BEOMONTH(CQ4%2C-1))%3C%2FPRE%3E%0A%3CP%3Ecould%20be%20changed%20on%3C%2FP%3E%0A%3CPRE%3ENOT(ISNUMBER(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)))%3C%2FPRE%3E%0A%3CP%3EISNUMBER%20here%20is%20to%20consider%20both%20blank%20and%20text%20cells%20(%22N%2FA%22)%20as%20not%20completed%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284328%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284328%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%26nbsp%3B%20Nice%20work%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bbut%20just%20to%20clarify%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F237675%22%20target%3D%22_blank%22%3E%40Tyler%20Smith%3C%2FA%3E%2C%26nbsp%3B%20if%20an%20item%20is%20completed%20in%20October%202017%20and%20planned%20for%20October%202018%26nbsp%3B%20did%20you%26nbsp%3B%20want%20that%20planned%20October%202018%20item%20to%20be%20included%20in%20the%20planned%20but%20not%20completed%20count%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESergei's%20formula%20is%20giving%20the%20result%20of%20all%20items%20planned%20in%20October%20v%20all%20items%20completed%20in%20October%20regardless%20of%20whether%20they%20are%20related%20to%20each%20other.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20whether%20the%20%22Items%20due%20not%20completed%22%20result%20is%20meaningful%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59331i3C648870E018C2A4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283994%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283994%22%20slang%3D%22en-US%22%3E%3CP%3ETyler%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283993%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283993%22%20slang%3D%22en-US%22%3E%3CP%3EWow%20thank%20you%20so%20much!!%20I%20would%20of%20never%20even%20thought%20of%20going%20that%20route%20with%20the%20formula%20but%20after%20testing%20it%20is%20working%20perfect!%20Thank%20you%20again.%20This%20is%20the%20best%20forum%20ever!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283969%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283969%22%20slang%3D%22en-US%22%3E%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(ISNUMBER(SEARCH(%22Plan%22%2C%24R%243%3A%24BA%243))*(%24R%244%3A%24BA%2434%26gt%3B%3D(EOMONTH(CQ4%2C-1)%2B1))*(%24R%244%3A%24BA%2434%26lt%3B%3DEOMONTH(CQ4%2C0))*((OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)%26lt%3BEOMONTH(CQ4%2C-1))%2B(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)%26gt%3BEOMONTH(CQ4%2C0))))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283949%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283949%22%20slang%3D%22en-US%22%3EAnyone%20else%20able%20to%20suggest%20a%20solution%20to%20Tyler%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283461%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283461%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20would%20work%20for%20a%20small%20batch%20of%20data%20but%20with%2036%20columns%20and%20ever%20expanding%20rows%20I%20would%20be%20worried%20about%20something%20getting%20missed%20along%20the%20way%20and%20giving%20wrong%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283217%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283217%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Tyler%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMy%20approach%20would%20be%20to%20add%20some%20helper%20columns%20for%20Year%20and%20Month%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETake%20a%20look%20at%20the%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283167%22%20slang%3D%22en-US%22%3ERE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283167%22%20slang%3D%22en-US%22%3EDoes%20anyone%20have%20any%20idea%20of%20how%20to%20do%20this%3F%20It's%20driving%20me%20crazy!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482004%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482004%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%26nbsp%3Bknow%20I%20am%20reaching%20back%20a%20while%2C%20but%20I%20figured%20since%20I%20already%20have%20this%20thread%20and%20my%20problem%20has%20to%20do%20with%20the%20same%20problem%20i%20would%20continue%20it%20on.%20So%20my%20problem%20now%20is%20I%20need%20to%20Compare%20both%20of%20my%20columns%20(Plan%20%2C%20Actual)%2C%20if%20the%20Plan%20has%20a%20date%20within%20a%20certain%20month%2C%20(lets%20say%20march)%2C%20then%20I%20need%20the%20formula%20to%20look%20at%20the%20actual%20and%20see%20if%20it%20was%20also%20completed%20in%20the%20same%20month.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482144%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482144%22%20slang%3D%22en-US%22%3EYou%20have%20terminated%20this%20conversation.%20Thus%2C%20I%20advise%20you%20to%20start%20anew.%20Otherwise%2C%20this%20thread%20might%20go%20on%20forever!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482176%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482176%22%20slang%3D%22en-US%22%3E%3CP%3EI%20figured%20it%20would%20be%20easier%20for%20everyone%20instead%20of%20having%20to%20re%20explain%20the%20entire%20situation.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482266%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F237675%22%20target%3D%22_blank%22%3E%40Tyler%20Smith%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20mean%20to%20calculate%20number%20of%20plans%20completed%20at%20the%20same%20month%20as%20planned%3F%20When%20it's%20only%20to%20adjust%20a%20bit%20second%20part%20of%20the%20previous%20formula%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(%0A%20%20%20ISNUMBER(SEARCH(%22Plan%22%2C%24R%243%3A%24BA%243))*%0A%20%20%20(%24R%244%3A%24BA%2434%26gt%3B%3D(EOMONTH(CQ4%2C-1)%2B1))*%0A%20%20%20(%24R%244%3A%24BA%2434%26lt%3B%3DEOMONTH(CQ4%2C0))*%0A%20%20%20(ISBLANK(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1))%2BISNUMBER(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1))*(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)%26gt%3B%3D(EOMONTH(CQ4%2C-1)%2B1)))*%0A%20%20%20(ISBLANK(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1))%2BISNUMBER(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1))*(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)%26lt%3B%3DEOMONTH(CQ4%2C0)))%0A)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482290%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482290%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20almost%20what%20I%20am%20looking%20for%2C%20the%20only%20thing%20is%20that%20it%20is%20counting%20the%20items%20as%20%22completed%22%20before%20the%20actual%20column%20has%20been%20entered.%20Once%20the%20actual%20column%20had%20been%20entered%20it%20does%20work%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20would%20like%20it%20to%20look%20at%20the%20%22Plan%22%20Column%20and%20if%20it%20is%20due%20in%20that%20month%2C%20then%20look%20at%20the%20%22Actual%22%20column%20to%20see%20if%20that%20task%20was%20completed%20in%20that%20month.%20But%20like%20I%20said%20above%20I%20need%20it%20to%20not%20count%20anything%20in%20the%20%22Plan%22%20Column%20until%20a%20date%20has%20been%20entered%20in%20the%20%22Actual%22%20column.%20I%20hope%20this%20clears%20up%20what%20I'm%20trying%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20apologize%20for%20the%20confusion.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482333%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482333%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F237675%22%20target%3D%22_blank%22%3E%40Tyler%20Smith%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20in%20initial%20formula%20was%20%22blank%20or%2C%20if%20date%2C%20in%20later%20month%22%2C%20I%20forgot%20to%20remove%20checking%20on%20blank.%20Check%20if%20text%20here%20or%20not%20also%20could%20be%20removed%2C%20only%26nbsp%3B%22within%20the%20month%22%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(%0A%20%20%20ISNUMBER(SEARCH(%22Plan%22%2C%24R%243%3A%24BA%243))*%0A%20%20%20(%24R%244%3A%24BA%2434%26gt%3B%3D(EOMONTH(CQ4%2C-1)%2B1))*%0A%20%20%20(%24R%244%3A%24BA%2434%26lt%3B%3DEOMONTH(CQ4%2C0))*%0A%20%20%20(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)%26gt%3B%3D(EOMONTH(CQ4%2C-1)%2B1))*%0A%20%20%20(OFFSET(%24R%244%3A%24BA%2434%2C0%2C1)%26lt%3B%3DEOMONTH(CQ4%2C0))%0A)%3C%2FPRE%3E%0A%3CP%3EI%20didn't%20check%20the%20source%20data%20if%20result%20is%20correct%2C%20please%20inform%20if%20something%20is%20wrong%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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

@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

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