Results 1 to 8 of 8

Thread: Sumif (2000)

  1. #1
    New Lounger
    Join Date
    Jun 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumif (2000)

    I have used your lounge previously and found it very useful. I currently have, what I am sure is an extremely simple problem to solve, but am finding it frustrating as I cannot think of a solution.
    I wish to sum cells J8:L8 and deduct the sum cells G8:I8 if the contents in cell I6 is contained in the range E14:E25 and put the result in cell I23.
    Any help would be gratefully received.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif (2000)

    Welcome to Woody's Lounge!

    Does the following formula do what you want?
    <code>
    =SUM(J8:L8)-IF(NOT(ISNA(MATCH(I6,E14:E25,0))),SUM(G8:I8))
    </code>
    MATCH(I6,E14:E25,0) checks if the value of I6 can be found in E14:E25, it returns an index number if found, or #N/A if not. NOT(ISNA(MATCH(I6,E14:E25,0))) is TRUE if the value was found, FALSE if not.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumif (2000)

    I am not exactly clear what you want, here is a guess. Put this in I23

    <pre>=SUM(J8:L8)-ISNUMBER(MATCH(I6,E14:E25,0))*SUM(G8:I8)</pre>


    It sums the values in J8:L8. If the contents of I6 is in the range E14:E25, it will subtract the sum of G8:I8, otherwise it will just be the sum of J8:L8.

    Steve

  4. #4
    New Lounger
    Join Date
    Jun 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif (2000)

    Thanks Hans for ypur very rapid response. Unfortunately it didn't do what I wanted. I have probably done something wrong.
    Please see the attachment. What I want is for the result that appears in cells G23 to V23, if the contents in cell in the same column at row 12 is included in the range E14:E25
    So in the attachment the result in cell I23 should be 0 as DE4 is not included in the range E14:E25.
    Hopefully this is more understandable.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif (2000)

    Does this do what you want?

    =IF(NOT(ISNA(MATCH(I$12,$E$14:$E$25,0))),SUM(J$17: L$17)-SUM(G$17:I$17),0)

    or

    =ISNUMBER(MATCH(I$12,$E$14:$E$25,0))*(SUM(J$17:L$1 7)-SUM(G$17:I$17))

  6. #6
    New Lounger
    Join Date
    Jun 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif (2000)

    Thats magic, thanks Hans, and Steve. Its exactly what I wanted, I can see that I will be calling on you guys again.
    Could you just tell me what ISNA and ISNUMBER actually mean?
    Thanks again.

    Regards
    Garry

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif (2000)

    The ISNA function looks at its argument, and returns TRUE if it is (or results in) #N/A, and FALSE otherwise. (Note: N/A stands for Not Available)
    The ISNUMBER function looks at its argument, and returns TRUE if it is (or results in) a valid number, and FALSE otherwise.
    You can look up these functions in the built-in help for Excel, it provides the syntax and some examples.

  8. #8
    New Lounger
    Join Date
    Jun 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif (2000)

    Thanks Hans, you've been a great help.

    Regards
    Garry

Posting Permissions

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