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]

Excel Workbook
	A	B	C	D
12	SKU	Category	Price	Stock
13	A01	Socks	10	1
14	A02	Socks	8	1
15	A03	Shoes	99	2
16	A04	Shoes	69	1
17	A05	Socks	5	10
18	A06	Shoes	99	1
19
20		Socks	23	12
21		Shoes	267	4
sheet 1

Cell Formulas
Range	Formula
C20	=SUMPRODUCT(($B$13:$B$18=$B20)*(C$13:C$18))
C21	=SUMPRODUCT(($B$13:$B$18=$B21)*(C$13:C$18))
D20	=SUMPRODUCT(($B$13:$B$18=$B20)*(D$13:D$18))
D21	=SUMPRODUCT(($B$13:$B$18=$B21)*(D$13:D$18))

Is this what you mean?

And for your 2e question.

Excel Workbook
	E	F
20	8%	75%
21	92%	25%
sheet 1

Cell Formulas
Range	Formula
E20	=C20/SUM(C$20:C$21)
E21	=C21/SUM(C$20:C$21)
F20	=D20/SUM(D$20:D$21)
F21	=D21/SUM(D$20:D$21)

 

[BiocideJ]

How about the following:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	SKU	Category	Price	Stock		Category	Total Price	Contribution of Total Price	Total Stock	Contribution of Total Stock
2	A01	Socks	10	1		Socks	18	6.2%	2	12.5%
3	A02	Socks	8	1		Shoes	272	93.8%	14	87.5%
4	A03	Shoes	99	2
5	A04	Shoes	69	1
6	A05	Shoes	5	10
7	A06	Shoes	99	1
Sheet1

Cell Formulas
Range	Formula
G2	=SUMIF(B:B,F2,C:C)
G3	=SUMIF(B:B,F3,C:C)
H2	=G2/SUM(G:G)
H3	=G3/SUM(G:G)
I2	=SUMIF(B:B,F2,D:D)
I3	=SUMIF(B:B,F3,D:D)
J2	=I2/SUM(I:I)
J3	=I3/SUM(I:I)

 

[Robert Mika]

Excel 2010
	A	B	C	D
1	SKU	Category	Price	Stock
2	A01	Socks	10	1
3	A02	Socks	8	1
4	A03	Shoes	99	2
5	A04	Shoes	69	1
6	A05	Socks	5	10
7	A06	Shoes	99	1
Stock

Excel 2010
	A	B	C	D	E	F
1	Category	TotalPrice	ContributionOfTotalPrice	TotalStock	ContributionOfTotalStock
2	Socks	23	8%	12	75%
3	Shoes	267	92%	4	25%
Totals

Cell Formulas
Range	Formula
B2	=SUMIF(Stock!$B$2:$B$7,$A2,Stock!$C$2:$C$7)
C2	=$B2/SUM($B$2:$B$3)
D2	=SUMIF(Stock!$B$2:$B$7,$A2,Stock!$D$2:$D$7)
E2	=$D2/SUM($D$2:$D$3)

 

[pkiula]

Thanks. Wow, this is incredible. What a community!

Just one newbie question: in all these formulas above, where is it checking that the value of the column should be "Socks" or "Shoes" (string literals)?

In my real worksheet, I have tens of thousands of rows, so I am looking for a formula that just takes a column if possible, not a specific range. Otherwise I'll be doing range selections forever! I have 261 product types, not just two (socks, shoes).

Thanks!

 

[pkiula]

Thanks. Wow, this is incredible. What a community!

Just one newbie question: in all these formulas above, where is it checking that the value of the column should be "Socks" or "Shoes" (string literals)?

In my real worksheet, I have tens of thousands of rows, so I am looking for a formula that just takes a column if possible, not a specific range. Otherwise I'll be doing range selections forever! I have 261 product types, not just two (socks, shoes).

Thanks!

Sorry, spoke too soon. In biocide's code

=SUMIF(B:B,F2,C:C)

The "B:B" is the entire column, which is superb. This is what I was looking for.

Now, the "F2" is hardcoded though. Is there a way to do this such that the list of totals is automatically generated for each unique value in column B, and listed by itself in F, and then the totals generated correspondingly?

I basically don't want to manually fill the rows of the F column and then test against it.

Thanks!

 

[Oeldere]

In my example the values are in B20 en B21.

But in your case I would use a pivot table.

 

[Robert Mika]

To get unique values
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Category</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Shoes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Socks</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Socks</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Shoes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Caps</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">Shirt</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Category</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Shoes</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Socks</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Caps</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Shirt</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table>

Sheet8​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E9</th><td style="text-align:left">{=INDEX(Stock,MATCH(0,COUNTIF($E$8:E8,Stock),0))}</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E10</th><td style="text-align:left">{=INDEX(Stock,MATCH(0,COUNTIF($E$8:E9,Stock),0))}</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E11</th><td style="text-align:left">{=INDEX(Stock,MATCH(0,COUNTIF($E$8:E10,Stock),0))}</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E12</th><td style="text-align:left">{=INDEX(Stock,MATCH(0,COUNTIF($E$8:E11,Stock),0))}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Workbook Defined Names<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">Stock</th><td style="text-align:left">=Sheet8!$A$2:$A$20</td></tr></tbody></table></td></tr></tbody></table>

 

[BiocideJ]

To create your unique list, you can use the Advanced Filter.
In Excel 2007 that is Data > Filter > Advanced

Select Copy to another location
List Range: the range of all your Categories (i.e. B2:B999)
Criteria Range: (leave blank)
Copy To: F2
Check the box for Unique Records Only.

Magically, you will have a list of all of the unique entries from column B in column F and can then just drag down the formulas for G:J for each row.

 

[pkiula]

Biocide, thanks, but advanced filter is not future-proof. It's a "moment in time" type of thing. I'd like to have a setup where if a new product, say "Toaster", is added to the original list, then the list of totals is also updated automagically with a new row and totals for "Toaster". With advanced filters this won't happen.

Robert, same problem with your approach which requires that I manually test for each product type (category).

Any other ideas?