Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    England
    Posts
    26

    Unanswered: quick question about if statement

    hello everyone

    I am doing some array formulas to summarise a list of data.

    I want to sum withdrawals and closures together, can anyone let me know how to go about it.

    This is what I have got so far

    =SUM(IF($C$5:$C$35="S",IF($D$5:$D$35="W",IF($E$5:$ E$35<>"IT",IF($I$5:$I$35=1,$F$5:$F$35,0),0),0)))


    When I add another criteria for closures then it just excludes all the others, cloumn D cant be W - withdrawal and C - closure, it would just be one or the other.

    thanks
    Beth

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    If I understand what you are trying to do, then I think your problem is nesting the IF statement within the SUM function. Try it this way (keep in mind that you do not enter the { } brackets - those are entered by Excel when you press CTRL + Shift + Enter):

    {=IF(($C$5:$C$35="S")+($D$5:$D$35="W")+($E$5:$E$35 ="IT")+($I$5:$I$35=1),1,0)*($F$5:$F$35)}

    The + sign functions as "OR" so multiple conditions are met.
    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
    Jul 2003
    Location
    England
    Posts
    26
    How do I do it so that only column D is "or" criteria, all of the others are "and"

    This is my criteria

    Cloumn C is S
    Column D is W or C
    Column E is IT
    Column I is 1
    Calcluate the total of column F

    Thanks,
    Beth
    Beth

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    For AND operation use * instead of +
    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
    Oct 2003
    Posts
    1,091
    Going back to your original formula, don't make it nested - it won't work: something like this

    =SUM(IF(($C$5:$C$35="S")*(($D$5:$D$35="W")+($D$5:$ D$35="C"))*($E$5:$E$35<>"IT")*($I$5:$I$35=1),$F$5: $F$35,0))

    I tried this and it works well. Be sure that you use CTRL + SHIFT + ENTER, and not ENTER.
    Last edited by shades; 11-12-03 at 12:13.
    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

Posting Permissions

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