Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Combination of IF and AND

    Hi-
    I am trying to have excel sum certain columns if another column = 1 and the sum of those columns is over a certain amount. I keep getting FALSE for the answer, so it must be missing the next argument. Any ideas?

    =IF(AND(C3="1"),IF(H3>500,SUM(D3+E3+F3-500),IF(H3>250,SUM(D3+E3+F3-250)," ")))

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    A couple of things:

    Take out the " around 1, since it is a number (only text requires the quotes).

    Also, your AND function does not include the second condition. You have too many IF's

    Can you elaborate a little more on what you are trying to accomplish?

    [EDIT:

    Is this what you want?

    =IF(AND((C3=1)*(H3>500)),D3+E3+F3-500,IF(AND((C3=1)*(H3>250)*(H3<500)),D3+E3+F3-250,""))


    BTW, the space between the last set of quotes should be deleted if you want nothing to show.

    On second edit, I think this will achieve what you want.
    [/EDIT]
    Last edited by shades; 02-02-04 at 12:37.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Posts
    3
    Thanks for the help. I really appreciate it.

    Basically, I want Excel to put in the differenct between the sum of D3+E3+F3 and 500 if the sum of D3+E3+F3 is greater than 500 and the value of C3 = 1. If C3 <> 1 I want excel to put in the difference between the sum of D3+E3+F3 and 250 if the sum of D3+E3+F3 is greater than 250. SO it needs to check two things, 1) what C is equal to, and 2) what is the value of D3+E3+F3 and is it greater than X.




    Originally posted by shades
    A couple of things:

    Take out the " around 1, since it is a number (only text requires the quotes).

    Also, your AND function does not include the second condition. You have too many IF's

    Can you elaborate a little more on what you are trying to accomplish?

    [EDIT:

    Is this what you want?

    =IF(AND((C3=1)*(H3>500)),D3+E3+F3-500,IF(AND((C3=1)*(H3>250)*(H3<500)),D3+E3+F3-250,""))


    BTW, the space between the last set of quotes should be deleted if you want nothing to show.

    On second edit, I think this will achieve what you want.
    [/EDIT]

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Try this:

    =IF(AND((C3=1)*(D3+E3+F3)>500),D3+E3+F3-500,IF(AND(C3<>1)*((D3+E3+F3)>250),D3+E3+F3-250,""))
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Feb 2004
    Posts
    3

    Thanks!

    Thanks so much! I was going in circles...


    Originally posted by shades
    Try this:

    =IF(AND((C3=1)*(D3+E3+F3)>500),D3+E3+F3-500,IF(AND(C3<>1)*((D3+E3+F3)>250),D3+E3+F3-250,""))

Posting Permissions

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