+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : how to add only positive numbers in my SUMIF statement

  1. 03-10-2010, 11:39 AM #1
    cjwanat
    cjwanat is offline
    Registered User
    Join Date
    03-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    12

    how to add only positive numbers in my SUMIF statement

    I have a SUMIF statement that adds and subracts positive and negative numbers and works great for keeping a running total of what I have on hand. My statement is below.

    =SUMIF(Processing!D:D,Inventory!E2,Processing!B:B)

    Can I somehow make it where it only adds the positive numbers in the "B" column based on the other criteria?
    Last edited by cjwanat; 03-10-2010 at 04:24 PM.
    Reply With QuoteRegister To Reply
  2. 03-10-2010, 11:42 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: how to add only positive numbers in my SUMIF statement

    You mean you want same summing but only when column D is greater than 0?

    Try
    =SUMPRODUCT(--(Processing!$D$1:$D$100=Inventory!E2),--(Processing!$D$1:$D$100>0),Processing!$B$1:$B$100)

    note: In XL2003 and earlier you cannot use whole column references with this formula.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 03-10-2010, 01:09 PM #3
    cjwanat
    cjwanat is offline
    Registered User
    Join Date
    03-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: how to add only positive numbers in my SUMIF statement

    Not quite...

    My inventory sheet "E2" is a model name. lets call it "screw-x".and I ordered 1000 of them. "E3" on my inventory list is say "screw-y" and I ordered 1000 of them also.

    Now on my processing sheet column "D" I also have model names of items coming and going and in column "B" I have the quantity of those items.for instance on my processing sheet on 3/3/2010 I recieve 400(in column "B") "screw-x'(in cloumn "D")parts and on 3/4/2010 I recieve another 400(in column "B") "screw-x"(in column "D")parts and on 3/5/2010 I shipped -800(in column "B") "screw-x"(in column "D")parts.

    Using my SUMIF statement =SUMIF(Processing!D:D,Inventory!E4,Processing!B:B) I will show to have 0 on hand in my "on hand " column because they are all gone. So I need a way to only add up the positive numbers in the "B" column so I can show that 800 have come in with zero on hand and 200 left to recieve.

    I hope that explains it a little better.
    Reply With QuoteRegister To Reply
  4. 03-10-2010, 01:10 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: how to add only positive numbers in my SUMIF statement

    Similar Sumproduct statement:

    =SUMPRODUCT(--(Processing!$D$1:$D$100=Inventory!E2),--(Processing!$B$1:$B$100>0),Processing!$B$1:$B$100)
    Reply With QuoteRegister To Reply
  5. 03-10-2010, 01:23 PM #5
    cjwanat
    cjwanat is offline
    Registered User
    Join Date
    03-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: how to add only positive numbers in my SUMIF statement

    That equation only returns "#N/A" in the cell
    Reply With QuoteRegister To Reply
  6. 03-10-2010, 01:27 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: how to add only positive numbers in my SUMIF statement

    Are there any #N/A in the ranges?

    Maybe post a downscaled sample file...
    Reply With QuoteRegister To Reply
  7. 03-10-2010, 02:16 PM #7
    cjwanat
    cjwanat is offline
    Registered User
    Join Date
    03-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: how to add only positive numbers in my SUMIF statement

    In my attachment you can see that the "QTY ON HAND" works just fine but I need a way to only add the positive numbers in the "QTY RECEIVED" column. This will also help catch the double ships and over shipped items.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-10-2010, 02:45 PM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: how to add only positive numbers in my SUMIF statement

    I entered this formula:

    =SUMPRODUCT(--(Processing!$C$1:$C$100=Inventory!A2),--(Processing!$A$1:$A$100>0),Processing!$A$1:$A$100)

    and copied down.

    You might want to adjust the 100's in the formula to what might be the largest number of records you will ever see in Processing...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 03-10-2010, 03:30 PM #9
    cjwanat
    cjwanat is offline
    Registered User
    Join Date
    03-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: how to add only positive numbers in my SUMIF statement

    I see my problem from the last time I entered that formula into my main spreadsheet and you asked if I had any cells populated with #N/A. I have a Vlookup table in column C that returns a #N/A until another cell populates and I use an INDIRECT formula in conditional formating to make the font white in column "C" until it populates .

    Can you make your formula not look at or calculate the #N/A's in column "C"?
    Reply With QuoteRegister To Reply
  10. 03-10-2010, 03:40 PM #10
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: how to add only positive numbers in my SUMIF statement

    Change the Vlookup() formula to return a blank instead of #N/A

    =IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

    replace the VLookups with your Vlookup formulas..
    Reply With QuoteRegister To Reply
  11. 03-10-2010, 04:23 PM #11
    cjwanat
    cjwanat is offline
    Registered User
    Join Date
    03-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: how to add only positive numbers in my SUMIF statement

    Thanks that got it!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1