+ Reply to Thread
Results 1 to 4 of 4

Sumif and Sumifs Subtracting quantity twice

  1. #1
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    Sumif and Sumifs Subtracting quantity twice

    I volunteer for an Athletic Youth Club that needs to keep track of their inventory of basketball and soccer uniforms.

    Here are the worksheets and headers with problem data.

    INVENTORY sheet
    Column Headings:
    HTML Code: 
    The shorts (Shorts) are identified by size only. The quantities can be from 0 to the number of shorts in stock.
    The formula in Column(Sold) is:
    Please Login or Register  to view this content.
    Note:
    I use helper columns to identify unique jerseys and soccer sets, by size and jersey number, the quantities are always one (1). Above formula works as intended for jerseys and soccer sets (SocSet) using the Jersey# column.

    Problem: Shorts-AL were bought twice a total of 24. Then sold 2 of them, balance should be 22, but using above formula, it is 20, the -2 is deducted from both purchases

    The code I have now, works perfectly for sales of Jerseys and SocSet's, but not on generic products that don't have numbers on them also are bought in quantities and multiple times.

    The code I have, is looking up "all" rows with "Shorts-AL" and "subtracting" the qty from all rows where Sales!H:H column corresponds to "Shorts-AL".

    What I need to have is a code that will NOT subtract the qty from the Sales!H:H column from each row matching "Shorts-AL" in Inventory!G:G column, but DO subtract from only the first "Short-AL" that has in OnHand column a quantity equal to or more than the quantity sold. And if the quantity sold is "greater" than the quantity OnHand, subtract the balance from the "second" or Nth row matching "Shorts-AL".

    I did try to use sumproduct, but I messed up real bad and could not figure out if I should use "-" or "+" or "*".

    SALES sheet
    All sales are registered here.
    Column Headings:
    HTML Code: 
    I read that Access is better for tracking inventory, but could not find an appropriate template that keeps track of inventory by not only product type and size, but also jersey numbers on each uniform. Any help will be much appreciated. Have a great day!

    Thank you all for reading my post, and if you can help that would be great!
    JC

    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Sumif and Sumifs Subtracting quantity twice

    Hi,

    There might be a fancy formula to do what you are looking for, but I think the simplest and most effective process is to use a Pivot Table on your inventory data to efficiently consolidate duplicate inventory values.
    Then use your SUMIFS formula against the Pivot Table.

    Attached is an updated version of your sample file.

    Hope this is helpful.

    Cheers
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    Re: Sumif and Sumifs Subtracting quantity twice

    I'm looking for the fancy formula like I mentioned originally, I did try "sumproduct" but kept getting errors.

    I should have mentioned that I have created a sorted unique column (similar to your pivot table) and used the data from the Sales sheet directly. It works. But my goal is to show the OnHand quantities for each purchase, hence the need for the fancy formula.

    Sort of having a FIFO method, because eventually I will be adding COGS and value of inventory in stock.

    Thank you for your help.
    Cincin!

  4. #4
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Sumif and Sumifs Subtracting quantity twice

    OK, after skinning a few cats, this is what I came up with:

    Please Login or Register  to view this content.
    Enter it in row 2 and copy down.

    It calculates the total purchased product per the row it is on and subtracts the qty sold. Evaluates itself against the qty for that row: if it is less, it returns zero. if greater than or equal to, it returns that rows qty. Else it the difference of that rows qty and the qty sold.

    Hope that makes sense.

    Attached is a version of your example with the formulas.
    Also added a CF to highlight the rows that have similar purchases.

    Hope this makes sense and is helpful.

    Cheers

    p.s. No cats were actually harmed is the process for creating this formula.
    But I can't say the same for a few brain cells.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Update inventory worksheet by adding/subtracting stock with the quantity.
    By august20 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-17-2015, 03:46 AM
  2. Subtracting from the SUMIF with the difference from the SUMIF range
    By iamblue91 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2014, 09:01 PM
  3. [SOLVED] Sumif/Sumifs
    By kimsem in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2014, 09:37 PM
  4. Macro to SumIF a reference Quantity
    By dirock in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2012, 02:31 PM
  5. Adding and Subtracting quantity to an inventory list
    By theJARRETconcept in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-09-2011, 12:43 PM
  6. Sumifs Or Sumif?
    By Carp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2007, 03:27 PM
  7. Can I use multiple criteria in SUMIF funqtion?:quantity sold
    By Levan Alibegashvili in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-24-2005, 04:05 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1