[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.

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.

This works at my end

Formula:

Please Login or Register  to view this content.

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!

Try this: I think I edited it correctly.

Formula:

Please Login or Register  to view this content.

Or this (complete with file):

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

You are welcome. Glad to help.

@ Glenn
Yeah. Simpler/shorter.

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.