+ Reply to Thread
Results 1 to 8 of 8

Referencing a range of columns and rows with the IF function

  1. #1
    Cliff
    Guest

    Referencing a range of columns and rows with the IF function

    I am trying to use the following IF function to search a range of cells to
    find the number 1 and then return either 1 or leave the cell blank. However,
    it does not look at the range of cells.

    A4=IF(A1:AS3=1),1,""

    Any sugestions would help.
    --
    Cliff

  2. #2
    Peo Sjoblom
    Guest

    Re: Referencing a range of columns and rows with the IF function

    =IF(COUNTIF(A1:AS3,1)>0,1,"")

    --

    Regards,

    Peo Sjoblom

    Northwest Excel Solutions

    www.nwexcelsolutions.com

    (remove ^^ from email address)

    Portland, Oregon




    "Cliff" <Cliff@discussions.microsoft.com> wrote in message
    news:807BEF21-8669-4288-A5E6-60467FBF2DEF@microsoft.com...
    >I am trying to use the following IF function to search a range of cells to
    > find the number 1 and then return either 1 or leave the cell blank.
    > However,
    > it does not look at the range of cells.
    >
    > A4=IF(A1:AS3=1),1,""
    >
    > Any sugestions would help.
    > --
    > Cliff



  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    If you want to return 1 if 1 occurs anywhere in that range

    =IF(COUNTIF(A1:AS3,1),1,"")

  4. #4
    Cliff
    Guest

    Re: Referencing a range of columns and rows with the IF function

    Thanks, but what does the ,1 after the range A1:AS3 stand for?
    --
    Cliff


    "daddylonglegs" wrote:

    >
    > If you want to return 1 if 1 occurs anywhere in that range
    >
    > =IF(COUNTIF(A1:AS3,1),1,"")
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=528879
    >
    >


  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    In this formula

    =IF(COUNTIF(A1:AS3,1),1,"")

    the first 1 is what you're counting within the range. If the count is 0 then the formula will return "", i.e. a blank, if the count is anything else, i.e. 1 or higher the formula returns a 1.

    If you were searching for something else in the range, e.g. a letter x then the formula would be

    =IF(COUNTIF(A1:AS3,"x"),1,"")

  6. #6
    Cliff
    Guest

    Re: Referencing a range of columns and rows with the IF function

    I really need to just identify 1 only and nothing higher i.e =1
    --
    Cliff


    "daddylonglegs" wrote:

    >
    > In this formula
    >
    > =IF(COUNTIF(A1:AS3,1),1,"")
    >
    > the first 1 is what you're counting within the range. If the count is 0
    > then the formula will return "", i.e. a blank, if the count is anything
    > else, i.e. 1 or higher the formula returns a 1.
    >
    > If you were searching for something else in the range, e.g. a letter x
    > then the formula would be
    >
    > =IF(COUNTIF(A1:AS3,"x"),1,"")
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=528879
    >
    >


  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Not quite sure what you're saying. Do you want the formula to return a 1 only when there is only a single 1 in the range?

    If so

    =IF(COUNTIF(A1:AS3,1)=1,1,"")

  8. #8
    Cliff
    Guest

    Re: Referencing a range of columns and rows with the IF function

    I tried both of your formulas and they did not search the range of cells I
    believe. With 1 in all referenced cells the formula cell was blank.
    --
    Cliff


    "daddylonglegs" wrote:

    >
    > Not quite sure what you're saying. Do you want the formula to return a 1
    > only when there is only a single 1 in the range?
    >
    > If so
    >
    > =IF(COUNTIF(A1:AS3,1)=1,1,"")
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=528879
    >
    >


+ 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