IF Statement - refer to cells in a column
Results 1 to 8 of 8

Thread: IF Statement - refer to cells in a column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default IF Statement - refer to cells in a column

    Hi there,

    I am stumped as to how to construct this part of my IF formula

    Column
    F25 = 333,000.....G25 = 100,000
    F26 = 100,000.....G26 = 333,000
    F27 = 20,000.......G27 = 0
    F28 ....................G28
    F29 ....................G29
    F30 ....................G30

    This is my attempt but it's returning #VALUE !

    =IF(AND(G25:G30=0,F25:F30>0),TRUE,FALSE)

    I would like it to look if cell G25 = 0 and F25 > 0, or G26 =0 and F26 >0, or G27 = 0 and F27 >0, etc

    If any return True, then True, otherwise False. So above G27 and F27 return True, so overall formula return true.

    I would like to refer to the column rather than individual cells, as I have a large range to look at.

    Hopefully that makes some sense!!

    Thanks

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,611
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: IF Statement - refer to cells in a column

    Try this formula in any cell
    - it counts the number of times both conditions are met and returns true if one or more pairs satisfy the condition

    =COUNTIFS(F:F,">0",G:G,0)>0
    Last edited by Yongle; Jul 2nd, 2019 at 02:46 AM.

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,112
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: IF Statement - refer to cells in a column

    Try using OR instead of AND and confirm the formula with CTRL+SHIFT+ENTER.

    Or try COUNTIFS:

    =COUNTIFS(G25:G30,0, F25:G30,">0")>0
    If posting code please use code tags.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,112
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: IF Statement - refer to cells in a column

    Oops, slight typo in the formula I posted.

    Should be this.

    =COUNTIFS(G25:G30,0, F25:F30,">0")>0
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - refer to cells in a column

    Norie, thanks! That does work!!
    That gives me a great starting point to piece this particular formula together, which now should be possible.

    I don't suppose you could give me your thoughts on my second thread "IF Statement - need to reduce size drastically"?

  6. #6
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - refer to cells in a column

    Quote Originally Posted by Norie View Post
    Oops, slight typo in the formula I posted.

    Should be this.

    =COUNTIFS(G25:G30,0, F25:F30,">0")>0
    Could you please tell me what the >0 at the end of the formula is for?

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,611
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: IF Statement - refer to cells in a column

    Try the formula with and without >0 at the end and answer your own question

  8. #8
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - refer to cells in a column

    Thanks! Makes sense now..

    I've incorporated this into my new formula but it's not giving the result I was expecting;

    =IF(AND(COUNTIFS(G24:G33,0, F24:F33,">0")=0,AND(SUM(F24:G33)<>0),AND(COUNTIF(I24:I33,">25%"))),"No new transactions or variances +25%",)

    The first part = 0, the second part does not = 0, and the third part, there are no percentages over 25%. So it should return "No new transactions or variances +25%" but it's returning 0.

    Can you possibly see where I am going wrong?
    Last edited by tlc53; Jul 2nd, 2019 at 07:06 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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