0

I am by far not tech savvy enough to be doing this so I'm asking for help. I've created drop down lists to make my job easier for data entry however they want everything to total up a certain way and I'm not finding a way to do it. I have tried the all the =SUM... but my lists are on multiple sheets, and I need the total to only be a certain product. My function is coming out longer than they will allow so I need help. Without giving away what I'm working on I'll just lay it out and see if y'all can help.

If("product" in Incoming!B2:B,Outgoing!B2-B then Incoming!C2:C-Outgoing!C2-C)

Column B is the product and Column C is the amount of product brought in and sold All this information is going onto a sheet called Inventory.

I have spent countless hours googling, watching YouTube videos, searching through pre-made code and trying anything I can think of to find a solution. Maybe I'm just going crazy and trying to do something I have no clue how to do. Any help would be appreciated.

1
  • Hi and welcome. Without giving away what I'm working on That's a problem because it affects the information you provide us. May I try clarify? Please correct me if I'm wrong. You are tracking product quantities. You have at least two sheets "Income" and "Outgoing". A product on the Incoming sheet might (or not) be on the "Outgoing" Sheet. What you want is, for each product on the Incoming Sheet, you want to calculate the total quantity Incoming less the total quantity (if any) Outgoing. Is that right? Maybe you also want to do this with some date ranges but you haven't mentioned this.
    – Tedinoz
    Nov 2, 2021 at 8:46

1 Answer 1

0

Use this formula

=query({query(Incoming!A3:C,"select A, Sum(C) where A is not null GROUP BY A label Sum(C) ''",-1),iferror(arrayformula(vlookup(sort(unique(filter(Incoming!A3:A,Incoming!A3:A<>"")),1,1),query(Outgoing!A3:C,"select A, Sum(C) where A is not null GROUP BY A label Sum(C) ''",-1),2,0)),0)},"select Col1, Col2, Col3, Col2-Col3 label Col1 'Product',Col2 'Incoming',Col3 'Outgoing',Col2-Col3 'Net'")


Sample

screenshot

4
  • I actually have a 3rd Sheet that all the Information is going on for quick review. We are trying to keep an inventory of our products in our Shop. I can see about sharing more information. I apologize, my coworker dumped this project on me, and my boss asked about it last night. Nov 2, 2021 at 10:31
  • Not to worry. You can put this (or just about any) formula anywhere you like. In this example, the formula is on a third sheet. Is that what you mean? Or did you also want to pull some other/additional information onto a third sheet?
    – Tedinoz
    Nov 2, 2021 at 22:15
  • There is the "Inventory" sheet which is the main page where the I need it to auto update by adding the "Incoming" sheet and subtracting the "Outgoing" sheet. I think what's causing the issues is the drop down list. @Tedinoz Nov 3, 2021 at 9:12
  • what's causing the issues is the drop down list Riiight ;) You may know what this means but you're not explaining it to us. What issues, what dropdown lists? Earlier you said I've created drop down lists to make my job easier for data entry however they want everything to total up a certain way. Comments... 1) the content of the dropdown list(s) is unknown; 2) who/what is "they"? (manager or dropdowns or ???) - this is unknown; 3) the "certain way" for the totals is unknown. Would you please explain these things and/or any remaining issues. Please don't assume any prior knowledge by us.
    – Tedinoz
    Nov 3, 2021 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.