Forum Discussion
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 |
| Orange | 1 | Orange | 1 | Orange | 1 |
| Olive | 1 | Olive | 1 | Olive | 1 |
| Banana | 1 | Banana | 1 | Banana | 1 |
| Olive | 1 | Olive | 1 | Olive | 5 |
| Olive | 1 | Olive | 1 | Olive | 1 |
| Apple | 1 | Apple | 1 | Apple | 1 |
| Apple | 3 | Apple | 1 | Apple | 1 |
| Apple | 1 | Apple | 1 | Apple | 1 |
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.
10 Replies
One more similar variant for the collection
References:
=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 ) ) ) )Named ranges:
=SUM( MMULT( --( COLUMN(Headers) = CELL("col", Headers ) ), MMULT( TRANSPOSE( --(Data=$I2) ), IF( ISNUMBER(Data), Data, 0 ) ) ) )Table:
=SUM( MMULT( --( COLUMN(Shops[#Headers]) = CELL("col", Shops[#Headers] ) ), MMULT( TRANSPOSE( --(Shops=$I2) ), IF( ISNUMBER(Shops), Shops, 0 ) ) ) )- That's pretty devious. The conversion to a Table should make the worksheet far easier to use though.
That was only to illustrate differences in using range references, named ranges and tables when the size of the range is relatively dynamic. For that the person need to play with variants using this or that formulae suggested.
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.
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.
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.
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.
