Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF Formula (Excel 97)

    Hello,

    Can you write a SUMIF statement so that it will be able to Sum only if the condition is not met? Almost like a SUMIFNOT type of formula?

    Example:
    Apple 10
    Beer 24
    Banana 10
    Orange 10
    Rum 10

    The formula will read the data and sum only if it is not Apple, Banana, or Orange. Therefore it will total 34.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: SUMIF Formula (Excel 97)

    How about:

    =SUM(arg)-SUM(SUMIF(arg, "banana"),SUMIF(arg, "apple"),SUMIF(arg, "orange"))
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Formula (Excel 97)

    Or ctrl/shift/enter:
    =SUM(OFFSET(rg,,1)*(rg<>"Apple")*(rg<>"Banana")*(r g<>"Orange"))

  4. #4
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Formula (Excel 97)

    Thanks I'll try that.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SUMIF Formula (Excel 97)

    Bob, can you explain your formula? I'm <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> and too <img src=/S/snore.gif border=0 alt=snore width=32 height=15> to figure it out. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Formula (Excel 97)

    Sure! Assume rg is defined as A1:A5 and contains Apple, Pear,Banana,Orange,Grape and B1"B5 contains 10,20,30,40,50.
    An expression like rg<>"Apple" would compare each item of rg against the string "Apple" and return something like {FALSE;TRUE;TRUE;TRUE;TRUE} where TRUE means that item of rg is NOT = "Apple"
    TRUE * TRUE is 1, all other combinations produce 0.
    So, multiplying these rg<>"whatever" is something like
    {FALSE;TRUE;TRUE;TRUE;TRUE} * {TRUE;FALSE;TRUE;TRUE;TRUE} which is {0;0;1;1;1}.
    Offset(Rg,,1) is the column of #s next to rg, or the 10,20,30,40,50. Multiplying it all out is like:
    {10;20;30;40;50}*(0;1;1;1;1}*{1;1;0;1;1}*{1;1;1;0; 1} which is {0;20;0;0;50}.
    This is then passed into the SUM, for a result of 70.
    Capisce?

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SUMIF Formula (Excel 97)

    That makes sense as long as I don't think about it. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Formula (Excel 97)

    The other tips are great...
    but for real world applications, I think I would use a 3rd column (Col A1:A5 in my below example) for *Category* . In your example, you wanted the sum of booze

    A) I would *sumif* for booze in A6 -- the advantage here is that if your list of categories include veggies, booze, soft drinks etc then in A6, you can use Excel's *Pick From List...* function to automatically fill in that so called criteria selector cell.

    EXAMPLE: In one of my shop spreadsheets, i kept a list of over 150 automotive parts ordered over a period of time amounting to about 2,000 rows for a particular vehicle model. In the last row, I placed the sumif statements in the QTY, Price and Status columns and referenced their criterias to the selector cell directly under the part nomenclature column. I also tied the part number using VLOOKUP to the part nomenclature selector. Anytime I wanted to know about part X or whichever, I would right-click on the part name cell and click on PICK FROM LIST..., scroll down and click on the part I wanted to know about. The results would give me the qty ordered, total price and how many were filled and is great from a QA perspective for identifying parts and part numbers that constantly need replacing;

    [img]/forums/images/smilies/cool.gif[/img] In the event you want to mix it up as to what gets counted or not, then I would solve for *X*, where x marks the item I want counted *=SUMIF(A1:C5,A6,C1:C5)*. In column A, I would enter x in every cell that I want counted and also enter x in A6 (my criteria cell).

    Either case, I can solve for various scenarios on the fly by changing the criteria selector cell of the category column w/o re-editing the formula

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: SUMIF Formula (Excel 97)

    Bob,

    I've been playing with array formulas, as you know.

    So for this, I came up with
    {=SUM(ISNA(MATCH(items,exclude_list,0))*values)}
    where
    -items is a range containing the original list of fruits, drinks
    -values is a range to be summed
    -exclude_list is a list of items to be excluded from the sum

    With your tutorial, I think I'm getting the hang of these things now.

    Fred

  10. #10
    New Lounger
    Join Date
    Dec 2001
    Location
    Netherlands
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Formula (Excel 97)

    Hi Fred could you give me an Example sheet because I am having some difficulties.

    For the Items I can only select single cells no ranges as well as for the values ?

    Many thanks because the formule could be very very helpfull for me to automate a lott of stuff.

    Robert.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Formula (Excel 97)

    Gentlemen, etc.

    Another tip, which I worked out by implementing a similar strategy. I have a table of values, as follows:

    <pre>X Y X Y X Y
    1 5 3 9 4 7
    </pre>


    I need to calculate average, 3 sigma, range, and range/average in the following three ways: All, X only, Y only.

    For average ( X only shown below), the following array formula works great:

    <pre>{=SUM(IF(Header="X",A2:F2,0))/SUM(IF(Header="X",1,0))}
    </pre>


    It works because a "0" does not affect the resulting SUM's. However, for the other calculations, a "0" DOES adversely affect the calculation. For example, the following array formulas do not work for 3 sigma:

    <pre>{=3*STDEV(IF(Header="X",A2:F2,0))}
    </pre>

    ("0" is folded into the calculation when the header is "Y", the result is not accurate.)

    <pre>{=3*STDEV(IF(Header="X",A2:F2,))}
    </pre>

    (<nothing> also gets folded into the calulation as zero.)

    <pre>{=3*STDEV(IF(Header="X",A2:F2,NA()))}
    </pre>

    (the final result is #N/A)

    This one actually works:

    <pre>{=3*STDEV(IF(Header="X",A2:F2,""))}
    </pre>


    I think this works because "" is treated by the STDEV function (and other mathematical functions) as a non-numerical element, and is therefore ignored, instead of being treated as a numeric <nothing> and included in the calculation as a zero. For that matter, the following formula also works, I believe for the same reason:

    <pre>{=3*STDEV(IF(Header="X",A2:F2,"Nothing"))}
    </pre>


  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: SUMIF Formula (Excel 97)

    Robert,

    Attached is a sample sheet (not with the exact same items but the intent is the same) which shows both my array formula as well as a "normal" SUMIF approach to summing items not on an exclusion list. This was what the original problem was. As you can see, it's not clear that there's much difference to one approach vs the other. I'd be curious as to why you think the array formula approach can help you more than the SUMIF approach.

    Fred
    Attached Files Attached Files

Posting Permissions

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