• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF, AND formula

G

gtothek

Member
I'm having trouble nesting AND into my formula, the result needs to be on 2 conditions, the sales person's name and their start month. I can't get it to work, please help.

=IF(AND($H33="Newman",$E33>=201404,$H33="Milton",$E33>=201301,$H33="Johnson",$E33>=201403),"After","Before")
 
hi gtothek!

Can you please upload a sample file.. with manual expected result..
I have a doubt on the Start Month structure.. Its may be just a TEXT.

May be we can reduce the formula length if sample available..
 
@gtothek,

One obvious problem is that you are using AND criteria for three separate values of the same cell (i.e., $H33 = "Newman" AND $H33 = "Milton" AND $H33 = "Johnson"). The AND criteria can never be met in this case since that cell cannot be equal to three different values.

I suspect you are more interested in using a lookup table using functions such as INDEX/MATCH, VLOOKUP, etc. That will allow you to test the start month associated with the person while maintaining the flexibility for the table to be expanded for additional personnel.

Hope that at least helps you toward the right direction.

Regards,
Ken
 
I got it to work:

=IF(AND($H4="Stevens",$E4>=201404),"After",IF(AND($H4="Miller",$E4>=201207),"After",IF(AND($H4="Corher",$E4>=201403),"After",IF(AND($H4="Snell",$E4>=201203),"After","Before"))))
 
You must log in or register to reply here.
Back
Top