+ Reply to Thread
Results 1 to 6 of 6

An Aggregate and Index Formula Issue

  1. #1
    Registered User
    Join Date
    08-21-2017
    Location
    Virginia
    MS-Off Ver
    MS Office 2016
    Posts
    10

    An Aggregate and Index Formula Issue

    I have two spreadsheets in the same workbook: Inventory List and Inventory System

    In the Inventory System, I would like to be able to add the Quantity in Stock from the Inventory List (Column G) to the Quantity Received (Column K) in the Inventory System and have the Receiving Running Total in the Inventory System (Column L) reflect this.

    Also, in the Inventory System, I would like to be able to subtract the Quantity Issued in the Inventory System (Column O) from the Quantity in Stock in the Inventory List (Column G) and have the Issuing Running Total in the Inventory System (Column P) reflect this.

    The idea is to have running totals for issuing and receiving sorted by item (Column D) in the Inventory System and have the running totals reset when the item in the Inventory System (Column D) changes.

    My formula: =AGGREGATE(9,5,INDEX(K4:K37,MATCH(D4:D37,D4:D37,)):K4:K37)

    How can I revise this to accomplish this? THanks for the help.
    Last edited by AliGW; 11-12-2018 at 05:40 PM.

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,593

    Re: An Aggregate and Index Formula Issue

    I can't visualise this at all.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    08-21-2017
    Location
    Virginia
    MS-Off Ver
    MS Office 2016
    Posts
    10

    Re: An Aggregate and Index Formula Issue

    Attached is my Excel file
    Attached Files Attached Files

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,593

    Re: An Aggregate and Index Formula Issue

    Pleas re-read my previous post (Point 2) and amend your sheet accordingly. With a blank sheet to look at, I still can't visualise this.

  5. #5
    Registered User
    Join Date
    08-21-2017
    Location
    Virginia
    MS-Off Ver
    MS Office 2016
    Posts
    10

    Re: An Aggregate and Index Formula Issue

    The aggregate formula would go in the running total cell.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: An Aggregate and Index Formula Issue

    I believe that the way this should work is that the Inventory System (IS) sheet keeps up with the inventory totals on a event by event basis while the Inventory List (IL) sheet gives you a quick overview of what is in stock so that you will know when to reorder. To accomplish this put the beginning inventory in the first few rows of the IS sheet you could then populate:
    Column L using: =SUMIFS(K$4:K4,C$4:C4,C4)
    Column P using: =SUMIFS(O$4:O4,C$4:C4,C4)
    Column X using: =SUMIFS($K4:K$4,$C4:C$4,C4)-SUMIFS($O4:O$4,$C4:C$4,C4)+SUMIFS($S4:S$4,$C4:C$4,C4)
    On the IL sheet you could populate column G using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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: 7
    Last Post: 12-15-2017, 12:14 AM
  2. [SOLVED] SUM IF Formula issue
    By Obeitia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2016, 01:01 PM
  3. [SOLVED] SUM IF Formula issue
    By Obeitia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2016, 12:53 PM
  4. [SOLVED] Index Match Formula Issue - Lookup Value is a Formula
    By dodgemain in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2016, 11:48 AM
  5. Lookup Formula Issue, Search issue
    By kperitz in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-07-2014, 01:50 PM
  6. if (or....and....formula issue
    By step_one in forum Excel General
    Replies: 8
    Last Post: 12-18-2011, 03:37 AM
  7. Issue with Formula
    By oliver79 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-21-2008, 08:58 AM

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