+ Reply to Thread
Results 1 to 8 of 8

Sum of each item in a mixed list

  1. #1
    Registered User
    Join Date
    09-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    41

    Sum of each item in a mixed list

    Hello, I work in IT and I am trying to create a spreadsheet to keep track of the inventory in our storage room. So far, I have three sheets, named and used as follows:

    "Current Totals": (blank at the moment, see below)

    "Check In-Out": a running list for my team to add items as they add and subtract stuff from inventory. Column A is date, Column B is Tech name, Column C is the item name, and column D is qty. The Tech and Item columns have data validation from the "Data" sheet so that the Tech and Item names are always the same. Some examples of the item names are: "Laptop - HP ProBook 6530b", "Desktop - Dell OptiPlex 7020", "Printer/Scanner - Fujitsu fi-7160".

    "Data": contains a list of all the Tech names for column B in "Check In-Out", and a list of Item names for column C in "Check In-Out".

    So here's what I need help with. I want a view on the "Current Totals" sheet that lists each item and shows their current count from the numbers on the "Check In-Out" sheet. I'm just not sure how to go about doing a sum of each item from the list on the "Check In-Out" sheet all in one formula.

    Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,831

    Re: Sum of each item in a mixed list

    Can you post your workbook (or a reduced/anonomised version)?
    If someone has helped you then please add to their Reputation

  3. #3
    Registered User
    Join Date
    09-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Sum of each item in a mixed list

    Here is the file:

    Inventory.xlsx

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,306

    Re: Sum of each item in a mixed list

    A2, copied down...
    =IFERROR(INDEX('Check In-Out'!$C$2:$C$200,MATCH(0,INDEX(COUNTIF($A$1:A1,'Check In-Out'!$C$2:$C$200),),0)),"")
    B2, copied down...
    =COUNTIF('Check In-Out'!C:C,'Current Totals'!A2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    09-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Sum of each item in a mixed list

    It looks like that is working, except that I will eventually have negative numbers in column D on the Check In-Out as well as positive. I just tested by adding a -1 qty item, and it added it instead of subtracting it. I made a few changes to the workbook, so I will post it again here.

    Inventory.xlsx

    Thanks for the help thus far.

  6. #6
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,831

    Re: Sum of each item in a mixed list

    Quote Originally Posted by FDibbins View Post
    B2, copied down...
    =COUNTIF('Check In-Out'!C:C,'Current Totals'!A2)
    Shouldnt that be
    Please Login or Register  to view this content.
    ?

  7. #7
    Registered User
    Join Date
    09-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Sum of each item in a mixed list

    That did it, thanks much!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,306

    Re: Sum of each item in a mixed list

    Quote Originally Posted by pjwhitfield View Post
    Shouldnt that be
    Please Login or Register  to view this content.
    ?
    If they want to add, yes

    From the OP's post though...
    I want a view on the "Current Totals" sheet that lists each item and shows their current count from the numbers on the "Check In-Out" sheet
    I figured they wanted a count

+ 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. Replies: 1
    Last Post: 08-22-2014, 12:27 AM
  2. A column with mixed item list needs to be sorted out in separate columns
    By vishal karmocha in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-27-2014, 06:21 PM
  3. Need to pull out specific info from a mixed list
    By JessicaE in forum Excel General
    Replies: 2
    Last Post: 04-03-2014, 05:52 AM
  4. UserForm: Search Item in List > Call Table associated with Item > Execute Command
    By TexasAggie12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2014, 12:58 PM
  5. Help with VBA, find Item from list then sum totals of each item by type
    By HR_GUY in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-30-2013, 01:30 PM
  6. Taking the next item in a list into 1 of 2 tables based on the previous item time
    By SoleAris in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-07-2011, 04:59 PM
  7. linking a list item or text item in a user form to a worksheet
    By ravergirl7216 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-03-2010, 03:43 AM

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