Forum Discussion
Solved
Sum of All Product?
Hi, How do I get the total of all apples in the below table in one simple formula? Product QTY Product QTY Product QTY Apple 1 Apple 1 Apple 7 Apple 2 Apple 1 Apple 2 ...
Qinnab You could use SUMIF as demonstrated in the attached file. It's not very elegant though and perhaps your real life situation isn't as simplistic as your example suggests.
Qinnab How many stores? I suspect more than a couple, thus the formula will indeed get very clumsy with SUMPRODUCT or SUMIF.
Do you have any influence over how the store data get assembled? Why in columns, side-by-side? If you can get it all in three columns (Store, Product, Qty), you could do it in the blink of an eye with a pivot table.
I have 9 stores with 9 quantities in columns beside it, I have no control over storing this data since this is an output of several formulas within many sheets too. I believe Sum and Vlookup should be used here somehow! for now, I will settle with Sumif until I figure an easier way. Thank You very much for your help.
Qinnab The attached file now contains an example of a PQ solution.
Qinnab You're welcome! But if you get the data into an Excel file, you could just copy and paste the store info in one long list and then create the pivot table. Or if that is still too much work, consider PowerQuery (PQ). You can easily divide the information for the 9 stores (two columns each) into separate named ranges or tables. Connect to them with PQ, append them and clean-up a bit. Set it up correctly once, and you can use it over-and-over again.
