+ Reply to Thread
Results 1 to 5 of 5

creating a stock list - need to know how much material i have in stock

  1. 04-27-2016, 05:57 AM #1
    AleksBabic
    AleksBabic is offline
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    creating a stock list - need to know how much material i have in stock

    Good Morning,
    I am struggling with a formula I hope you can help.
    I am trying to create a "stock list" however I need to know how much material I have in stock at one time. I can have material that is sold but not delivered for financing terms this means it is still stock, but for salesman it is sold in their mind. I need a formula to tell me how much tonnage I have which is in "stock" / "reserved" / "sold - but not delivered".

    I feel I need a SUMIFS formula. in column A I have status of each item (ie. available/reserve/sold/invoiced etc.) so to find out how much material I have available I used the following formula: =SUMIFS(I:I,A:A,"available") . this works but now I need to do the same thing but showing "available" + "reserve" + "sold" but only "sold" if there is no delivery date in column AC.

    Please let me know if anybody can help. I have attached a spreadsheet showing example info I would have.

    Many Thanks in advance

    Aleks
    Attached Files Attached Files
    Last edited by AleksBabic; 04-27-2016 at 07:11 AM. Reason: changed title
    Reply With QuoteRegister To Reply
  2. 04-27-2016, 06:01 AM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: HELP PLEASE: Formula problems

    Please change the topic title according to the forumrules.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 04-27-2016, 06:10 AM #3
    AleksBabic
    AleksBabic is offline
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: creating a stock list - need to know how much material i have in stock

    Title changed - I have not used this forum in a long time. I apologise.
    Reply With QuoteRegister To Reply
  4. 04-27-2016, 06:50 AM #4
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,147

    Re: creating a stock list - need to know how much material i have in stock

    Try

    in D41

    =SUMPRODUCT(($I5:$I40)*(ISNUMBER(MATCH($A5:$A40,{"available","reserve","sold"},0)))*($AC5:$AC40=""))

    I limited the range to 40 rows as you have summary data in rows 41 down. I suggest you move those so they are not in any of your data columns (separate sheet?).

    With SUMPRODUCT limit the range to your likely maximum: do not use full columns.
    Reply With QuoteRegister To Reply
  5. 04-27-2016, 07:10 AM #5
    AleksBabic
    AleksBabic is offline
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: creating a stock list - need to know how much material i have in stock

    Thank you John, this has worked perfectly!
    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)

Similar Threads

  1. Formula problems
    By LostValleyBoy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2016, 08:46 AM
  2. Formula Problems
    By gec888 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-16-2015, 07:12 AM
  3. IF formula problems
    By ketco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-22-2015, 04:43 PM
  4. Formula problems
    By m.cardoso89 in forum Excel General
    Replies: 5
    Last Post: 03-13-2014, 02:45 PM
  5. Having Problems with IF Formula
    By IanMcC in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-22-2012, 11:46 AM
  6. Problems with a formula
    By vasluianuliviu in forum Excel General
    Replies: 5
    Last Post: 09-21-2006, 06:00 PM
  7. Formula Problems
    By Ted in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-21-2005, 04:20 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1