Multiple ifs not getting desired result

%3CLINGO-SUB%20id%3D%22lingo-sub-1682374%22%20slang%3D%22en-US%22%3EMultiple%20ifs%20not%20getting%20desired%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682374%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CDIV%20class%3D%22MessageSubject%22%3E%3CDIV%20class%3D%22MessageSubjectIcons%20%22%3E%3CH1%20id%3D%22toc-hId-531081584%22%20id%3D%22toc-hId-531081645%22%20id%3D%22toc-hId-531081645%22%20id%3D%22toc-hId-531081645%22%20id%3D%22toc-hId-531081645%22%20id%3D%22toc-hId-531081645%22%20id%3D%22toc-hId-531081645%22%20id%3D%22toc-hId-531081645%22%3E%3CSPAN%20class%3D%22lia-message-read%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fneed-right-formula-for-multiple-ifs%2Fm-p%2F1679669%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3ENeed%20right%20formula%20for%20multiple%20IFS%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FA%3E%3C%2FSPAN%3E%3C%2FH1%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%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(%20Row%2037%2C%20Col%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%3C%2FP%3E%3CP%3Ed.%20If%20end%20month%20and%20year%20(Col.%20F)%20is%20less%20than%20current%20month%2C%20again%200%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20excel%20for%20better%20reference.%20Please%20suggest%20the%20formula%20in%20cell.%20You%20can%20see%20the%20formula%20which%20I%20have%20used%20in%20the%20formula%20bar.%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1682374%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-1682597%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20ifs%20not%20getting%20desired%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682597%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20think%20you're%20missing%20a%20few%20%24%20signs%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFS(%24D41%26gt%3B%3DH%2437%2C0%2C%24E41%3D%22CEO%22%2C0%2C%24E41%3D%22Product%20Manager%22%2C1%2C%24F41%3CH%3E%0A%3C%2FH%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EApart%20from%20that%3A%20what%20should%20be%20returned%20if%20none%20of%20the%20conditions%20is%20met%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1683367%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20ifs%20not%20getting%20desired%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683367%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20get%20this%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1600427405882.png%22%20style%3D%22width%3A%20705px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219522i4406C73B6C385D68%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rajesh-S_0-1600427405882.png%22%20alt%3D%22Rajesh-S_0-1600427405882.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%2C%2C%26nbsp%3B%3C%2FSTRONG%3EOnly%20in%20Row%205%20you%20get%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20range%20G9%3AJ13%2C%20I've%20tested%20every%20IF.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20formula%20in%20G9%20is%20testing%20difference%20between%20Start%20Date%20%26amp%3B%20Current%20Date%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%24H2-L%241%3D0%2C0%2C1)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20for%20the%20FALSE%20condition%2C%20I've%20used%201%2C%20is%20NEXT%20IF%20in%20original%20formula%2C%20check%20result%20in%20Col%20K.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20in%20H9%20checks%20CEO%2C%20I9%20for%20Project%20Manage%20%26amp%3B%20J9%20is%20for%20Difference%20between%20End%20Date%20and%20Current%20date%2C%20check%20result%20in%20Col%20L.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EYou%20need%20to%20use%20tradition%20Nested%20IF%20instead%20of%20IFS%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%24H2-L%241%3D0%2C0%2CIF(%24I2%3D%22CEO%22%2C0%2CIF(%24I2%3D%22Pjoect%20Manager%22%2C1%2CIF(%24J2-L%241%26lt%3B0%2C0%2C%22No%20Match%22))))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi, 

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

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

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

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

d. If the end month and year (Col. F) is less than the current month, again 0

 

Attached excel for better reference. Please suggest the formula in column H. You can see the formula which I have used in the formula bar. 

2 Replies
Highlighted

@Nishantu309034 

I think you're missing a few $ signs:

 

=IFS($D41>=H$37,0,$E41="CEO",0,$E41="Product Manager",1,$F41<H$37,0)

 

Apart from that: what should be returned if none of the conditions is met?

Highlighted

You get this,,

 

Rajesh-S_0-1600427405882.png

 

Note,, Only in Row 5 you get 1.

 

In range G9:J13, I've tested every IF.

 

For example formula in G9 is testing difference between Start Date & Current Date,,

 

=IF($H2-L$1=0,0,1) 

 

and for the FALSE condition, I've used 1, is NEXT IF in original formula, check result in Col K.

 

Formula in H9 checks CEO, I9 for Project Manage & J9 is for Difference between End Date and Current date, check result in Col L.

 

You need to use tradition Nested IF instead of IFS:

 

=IF($H2-L$1=0,0,IF($I2="CEO",0,IF($I2="Pjoect Manager",1,IF($J2-L$1<0,0,"No Match"))))