I have a simple set of 3 tables here tracking what items were bought from certain departments and a 4th table compiling that info using Countif. When an item is bought, it is recorded on the department's corresponding table. If an item is returned, "Returned" is put on that specific item line and the Totals table captures that by subtracting from the total for that department. What I'm struggling to wrap my head around is the Total Sold by Item column - how do I capture a returned item in the horizontal calculation? In this example, 6 shirts were bought in total, but 2 were returned so what formula should I use in E2 (which should read 4) to capture the returned shirts from all departments?
I feel like the answer is obvious, but I can't seem to wrap my head around it.
Thank you in advance
Edit: Another thing I would like to know is if there is a way to quickly pull which items and from which departments were returned as well, haven't had a chance to think of it myself, just crossed my mind as I submitted this post.
Edit 2: I think I may have achieved what I needed using SUM COUNTIFS for each sheet and adding an extra column beside the total by item (and subtracting that column), rather than trying to fit the calculation into the total cell E2. My second still stands however, anyway to return which items were returned form where?
Bookmarks