+ Reply to Thread
Results 1 to 6 of 6

IF,AND Formula

  1. #1
    Shinka
    Guest

    IF,AND Formula

    Trying to get a cell to show a 1 (so I can add it), IF cell A1 is blank and
    A2 thru A6 has an "f" in any of them.

    If/AND formulas are killing me! Your help is appreaciated.

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451
    Try this:

    =IF(AND(A1="",COUNTIF(A2:A6,"f")>0),1,"")

    You didn't say what value you wanted if the 2 conditions are not met so I assumed you wanted a blank cell. Change the "" before the last bracket to something else if a blank cell is not what you want.

  3. #3
    Martin P
    Guest

    RE: IF,AND Formula

    This might help:
    =IF(AND(SUMPRODUCT(--(A2:A6="f"))>0,ISBLANK(A1)=TRUE),1,0)

    "Shinka" wrote:

    > Trying to get a cell to show a 1 (so I can add it), IF cell A1 is blank and
    > A2 thru A6 has an "f" in any of them.
    >
    > If/AND formulas are killing me! Your help is appreaciated.


  4. #4
    Shinka
    Guest

    RE: IF,AND Formula

    Thank You! I wrestled with this for 2 days. Nestled functions are kinda
    tough. Nice clean solution...thanks again.

    "Martin P" wrote:

    > This might help:
    > =IF(AND(SUMPRODUCT(--(A2:A6="f"))>0,ISBLANK(A1)=TRUE),1,0)
    >
    > "Shinka" wrote:
    >
    > > Trying to get a cell to show a 1 (so I can add it), IF cell A1 is blank and
    > > A2 thru A6 has an "f" in any of them.
    > >
    > > If/AND formulas are killing me! Your help is appreaciated.


  5. #5
    Shinka
    Guest

    Re: IF,AND Formula

    That is what I had but couldn't figure out how to nest the AND with the
    COUNTIF.

    Thanks - both will work.

    "Cutter" wrote:

    >
    > Try this:
    >
    > =IF(AND(A1="",COUNTIF(A2:A6,"f")>0),1,"")
    >
    > You didn't say what value you wanted if the 2 conditions are not met so
    > I assumed you wanted a blank cell. Change the "" before the last
    > bracket to something else if a blank cell is not what you want.
    >
    >
    > --
    > Cutter
    > ------------------------------------------------------------------------
    > Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
    > View this thread: http://www.excelforum.com/showthread...hreadid=473126
    >
    >


  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451
    You're welcome. Nice to hear that it helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1