+ Reply to Thread
Results 1 to 5 of 5

Counting Many Items' Days In Inventory

  1. #1
    Registered User
    Join Date
    09-22-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Counting Many Items' Days In Inventory

    Using Excel 2007, I have 1200+ items in inventory in Column A (each has its own ID#) and the inventory date in Column B. This returns a current total of 9581 entries. I need to know how many days each item has been in stock.

    Without having to manually input Days360 after each change in ID#, is there a formula to automatically count days in inventory and insert at each change in ID#?

    I copied and removed all duplicates and pasted below the data, starting in Column A Row 9586, and tried =COUNT($A$S:$A$9581,A9586,$B$2:$B:9581but that didn't work, and I also tried =Days360($A$S:$A$9581,A9586,$B$2:$B:9581without luck also. Thank you.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Counting Many Items' Days In Inventory

    Could you post a representative section of your data set and an indication of what you expect the solution to look like (calculated manually). This would help with the understanding of your question.
    Martin

  3. #3
    Registered User
    Join Date
    09-22-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Counting Many Items' Days In Inventory

    Thank you. Here is a sample where I manually input Days360 after Subtotaling by Inventory #:

    Inventory# Inventory Date
    16384 12/2/09
    16384 12/17/09
    16384 1/4/10
    16384 1/29/10
    16384 2/17/10
    16384 2/27/10
    16384 3/31/10
    16384 4/30/10
    16384 5/14/10
    16384 5/28/10
    176 Days in Inventory

    16772 12/2/09
    16772 12/17/09
    16772 1/4/10
    16772 1/29/10
    16772 2/17/10
    16772 2/27/10
    16772 3/31/10
    16772 4/30/10
    16772 5/14/10
    16772 5/28/10
    16772 7/30/10
    16772 8/18/2010
    16772 8/31/2010
    269 days in inventory

  4. #4
    Registered User
    Join Date
    09-14-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Counting Many Items' Days In Inventory

    try this

    A1 is the date of today, B3 is your date.. I would suggest C3 as a years in inventory for each item. It returns the amout of time in years. You can then do a sum or average
    =(A1-B3)/365.24

  5. #5
    Registered User
    Join Date
    09-22-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Counting Many Items' Days In Inventory

    Re: Using this: A1 is the date of today, B3 is your date.. I would suggest C3 as a years in inventory for each item. It returns the amout of time in years. You can then do a sum or average
    =(A1-B3)/365.24


    I would still have to manually do a sum or average for each item. I don't understand why my daily successful formula of =COUNT($A$S:$A$9581,A9586,$B$2:$B:958 for counting ITEMS by ID# doesn't work when I try to use a similar formula for counting DAYS in inventory by subbing DAYS for COUNT.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1