Sep 17 2021 06:26 AM
Sep 17 2021 06:26 AM
How do I get the total of all apples in the below table in one simple formula?
Sep 17 2021 06:55 AM
Thanks @Riny_van_Eekelen , it is a kind of real-life situation, I have multiple columns ( Stores) that I need to get the total of products in.
I know how to get it in long formula using sumproduct, but I still believe there is an easier way to do it, maybe Vlookup! I just can't figure it out.
Sep 17 2021 07:08 AM
@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.
Sep 17 2021 07:13 AM
Sep 17 2021 07:24 AM
@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.
Sep 17 2021 11:58 AM
Array methods will perform calculation using your data as it stands, but is better with Excel 365. The first approach works with legacy Excel
= SUMIFS(QtyTable, ProductTable, ProductList)
where the quantity table is simply the product table offset one cell to the right.
Sep 17 2021 01:15 PM
One more similar variant for the collection
=SUM( MMULT( --( COLUMN($B$2:$G$2) = COLUMN($B$2) ), MMULT( TRANSPOSE( --($B$3:$G$12=$I2) ), IF( ISNUMBER($B$3:$G$12), $B$3:$G$12, 0 ) ) ) )
=SUM( MMULT( --( COLUMN(Headers) = CELL("col", Headers ) ), MMULT( TRANSPOSE( --(Data=$I2) ), IF( ISNUMBER(Data), Data, 0 ) ) ) )
=SUM( MMULT( --( COLUMN(Shops[#Headers]) = CELL("col", Shops[#Headers] ) ), MMULT( TRANSPOSE( --(Shops=$I2) ), IF( ISNUMBER(Shops), Shops, 0 ) ) ) )