# Multiple ifs not getting desired result

Highlighted
Occasional Contributor

# Need right formula for multiple IFS

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

# Re: Multiple ifs not getting desired result

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

# Re: Multiple ifs not getting desired result

You get this,,

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.