+ Reply to Thread
Results 1 to 10 of 10

[SOLVED] Function to track how many items different warehouses have received / sent

  1. #1
    Registered User
    Join Date
    03-26-2021
    Location
    Germany
    MS-Off Ver
    MS Office 365
    Posts
    4

    [SOLVED] Function to track how many items different warehouses have received / sent

    Hi,
    twice a day i receive excel sheets containing "logs" of how many items (of a certain kind) different warehouses have received / sent. The context of those files looks similar to this:
    Initial_table.JPG

    Now i'd like to know how i can summarize the contents of the initial table to get a summary table containing only the warehouse location and their current sock. Similarly to this:
    Desired_summary.JPG

    I've tried to use the "FILTER" function in combination with the "SUM" function, but i can't seem to get it to work the way i want it to.
    Any help would be much appreciated.

    Thanks

    Edit: Attached an example file.
    Attached Files Attached Files
    Last edited by voxcon; 03-26-2021 at 01:56 PM. Reason: Attached and example file.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Function to track how man items different warehouses have received / sent

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn



  3. #3
    Registered User
    Join Date
    03-26-2021
    Location
    Germany
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: Function to track how man items different warehouses have received / sent

    Quote Originally Posted by Glenn Kennedy View Post
    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Hi Glenn,
    sorry, initially i didn't see that i was supposed to attach an example file. I've edited my post and added one.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,163

    Re: Function to track how many items different warehouses have received / sent

    This works at my end
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Registered User
    Join Date
    03-26-2021
    Location
    Germany
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: Function to track how many items different warehouses have received / sent

    Hi Dave,
    thanks for your reply. I'm currently installing the english version of office, because i was having trouble reproducing your function in the german version (different seperator and punctuation settings). I'll let you know once i get it to work.

    Thanks

    Edit: it works. Thank you very much!
    Last edited by voxcon; 03-26-2021 at 01:54 PM.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,163

    Re: [SOLVED] Function to track how many items different warehouses have received / sent

    Try this: I think I edited it correctly.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: [SOLVED] Function to track how many items different warehouses have received / sent

    Or this (complete with file):

    =SUMPRODUCT((Tabelle1[WS Location:]=[@[WS Location:]])*(Tabelle1[Action:]={"Received","Sent"})*{1,-1}*Tabelle1[Number of units:])
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-26-2021
    Location
    Germany
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: [SOLVED] Function to track how many items different warehouses have received / sent

    Thanks, to both of you. You helped me a lot!

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,163

    Re: [SOLVED] Function to track how many items different warehouses have received / sent

    You are welcome. Glad to help.

    @ Glenn
    Yeah. Simpler/shorter.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: [SOLVED] Function to track how many items different warehouses have received / sent

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. Calendar to track expense items
    By BRF in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2013, 05:33 PM
  2. How to see percentage of items received daily, weekly, monthly?
    By ConfusedCitizen in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-21-2013, 03:44 PM
  3. Need VBA code to track status of items in stock
    By adelkam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2013, 11:24 AM
  4. Improvement - Show ALL THREADS which received Rep in Latest Reputation Received Area
    By :) Sixthsense :) in forum Suggestions for Improvement
    Replies: 2
    Last Post: 02-12-2013, 03:27 AM
  5. Track expired items in separate sheet
    By ctgo4it in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-21-2012, 03:01 PM
  6. Print labels for items received on receiving report
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 09-21-2011, 04:01 PM
  7. Items / Warehouses
    By ElmerS in forum Excel General
    Replies: 6
    Last Post: 02-13-2010, 01:06 PM
  8. [SOLVED] Is there a template to track invoices billed and received
    By depodan in forum Excel General
    Replies: 1
    Last Post: 03-27-2006, 12:35 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