Sum (or count) the values in a column based on values in another, and output total

[pkiula]

Hi, I have a product inventory file that looks broadly like this:

SKU  Category  Price    Stock
A01    Socks     10       1
A02    Socks      8       1
A03    Shoes     99       2
A04    Shoes     69       1
A05    Socks      5      10
A06    Shoes     99       1

Now I want to print:

1. The total "price" of Socks and Shoes.
2. The total "inventory" contribution of Socks and Shoes (i.e., each's total inventory divided by the total inventory).


My new worksheet needs to look like this:

Category    TotalPrice   ContributionOfTotalPrice    TotalStock    ContributionOfTotalStock
Socks          23                8%                     12              75%
Shoes         269               92%                      4              25%

How would I go about doing these? Many thanks for any tips or pointers!

 

[Oeldere]

With a pivot table and then refresh.

 

[BiocideJ]

Robert Mika's solution in #8 should suffice. Just don't use C:C for the named range Stock.

I would just use something arbitrarily large. For instance C2:C9999
The reason is that array formula will run excessively slow when you are running it on the 1M+ rows that Excel 2007 and later have.