Need right formula for multiple IFS

%3CLINGO-SUB%20id%3D%22lingo-sub-1679669%22%20slang%3D%22en-US%22%3ENeed%20right%20formula%20for%20multiple%20IFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1679669%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%20the%20following%20logic%20with%20the%20below%20table%3A%26nbsp%3B%3C%2FP%3E%3CP%3Ea.%20If%20Prep%20start%20month%20and%20year%20is%20more%20than%20Current%20month%20(Col.%20H%20-%20Dec'20)%2C%20then%200%26nbsp%3B%3C%2FP%3E%3CP%3Eb.%20If%20a%20is%20false%2C%20then%20only%20check%20Responsible%20person.%20If%20responsible%20person%20is%20CEO%2C%20then%200%3C%2FP%3E%3CP%3Ec.%20If%20a%20is%20false%2C%20then%20only%20check%20Responsible%20Person.%20If%20responsible%20person%20is%20Product%20Manager%20then%201.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20a%20screen%20shot%20for%20better%20cell%20reference.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%20error2.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219201i4BE51F61E7155620%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Excel%20error2.JPG%22%20alt%3D%22Excel%20error2.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22723px%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3EPrep%20Start%20Month%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3EResponsible%20person%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%3CP%3ECuurent%20Month%3C%2FP%3E%3CP%3EDec-20%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%201%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3EShort%20term%20Savings%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%3CSTRONG%3EFormulae%20here%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%201%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3EDigital%20Gold%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F11%2F2020%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%201%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3E%3CDIV%3E%3CDIV%3ERD%2C%20Mutual%20Fund%20for%20mid%2Flong%20term%20needs%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%202%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3ERetirement%20Fund%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F01%2F2022%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3EProduct%20Manager%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%201%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3ELoan%20against%20Savings%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F03%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%203%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3EHospicash%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F04%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%203%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3E3%20Wheeler%20Insurance%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F10%2F2020%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%203%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3E4%20Wheeler%20Insurance%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F10%2F2020%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%203%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3E2%20Wheeler%20Insurance%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F10%2F2020%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%203%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3EGroup%20Mediclaim%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F05%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%203%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3ESachet%20Insurance%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F02%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%203%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3ETerm%20Insurance%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F10%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%203%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3EAccidental%20Insurance%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F10%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3ECEO%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%204%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3EEdu%20finance%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F11%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3EProduct%20Manager%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253px%22%3EType%205%3C%2FTD%3E%3CTD%20width%3D%22197px%22%3EAggregator%20for%20specific%20products%3C%2FTD%3E%3CTD%20width%3D%2282px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105px%22%3E01%2F05%2F2021%3C%2FTD%3E%3CTD%20width%3D%22154px%22%3EProduct%20Manager%3C%2FTD%3E%3CTD%20width%3D%22131px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1679669%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1679781%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20right%20formula%20for%20multiple%20IFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1679781%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796836%22%20target%3D%22_blank%22%3E%40Nishantu309034%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStarting%20in%20cell%20H38%2C%20that%20could%20be%3A%3C%2FP%3E%3CPRE%3E%3DIFS(D38-%24H%2437%26gt%3B0%2C0%2CE38%3D%22CEO%22%2C0%2CE38%3D%22Product%20Manager%22%2C1)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1679896%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20right%20formula%20for%20multiple%20IFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1679896%22%20slang%3D%22en-US%22%3EWorked%20like%20a%20charm%2C%20thanks%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi, 

I want to create the following logic with the below table: 

a. If Prep start month and year is more than Current month (Col. H - Dec'20), then 0 

b. If a is false, then only check Responsible person. If responsible person is CEO, then 0

c. If a is false, then only check Responsible Person. If responsible person is Product Manager then 1. 

 

Attached a screen shot for better cell reference. 

Excel error2.JPG

 

      
1  Prep Start MonthResponsible person

Cuurent Month

Dec-20

Type 1Short term Savings 01/02/2020CEOFormulae here
Type 1Digital Gold 01/11/2020CEO 
Type 1
RD, Mutual Fund for mid/long term needs
 01/09/2021CEO 
Type 2Retirement Fund 01/01/2022Product Manager 
Type 1Loan against Savings 01/03/2021CEO 
      
Type 3Hospicash 01/04/2021CEO 
Type 33 Wheeler Insurance 01/10/2020CEO 
Type 34 Wheeler Insurance 01/10/2020CEO 
Type 32 Wheeler Insurance 01/10/2020CEO 
Type 3Group Mediclaim 01/05/2021CEO 
Type 3Sachet Insurance 01/02/2021CEO 
Type 3Term Insurance 01/10/2021CEO 
Type 3Accidental Insurance 01/10/2021CEO 
      
Type 4Edu finance 01/11/2021Product Manager 
      
Type 5Aggregator for specific products 01/05/2021Product Manager 

 

 

3 Replies
Highlighted

Hello @Nishantu309034,

 

Starting in cell H38, that could be:

=IFS(D38-$H$37>0,0,E38="CEO",0,E38="Product Manager",1)
Highlighted
Worked like a charm, thanks
Highlighted

@Nishantu309034 

 

You're welcome!