Multiple ifs not getting desired result

Copper 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

@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?

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"))))