Summing Unique Combinations of Records in Data Table (like Pivot Table)

[jaysunice]

Here's my starting data:

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=64>Product</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>Code</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>3535</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>A1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>3535</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>A1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>3535</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>B3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>5444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>A1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>5444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>C3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>5444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>D5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>9</TD></TR></TBODY></TABLE>

Here's what I would like as a result:

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=64>Product</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>Code</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>3535</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>A1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>22</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>3535</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>B3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>5444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>A1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>5444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>C3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>5444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>D5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>9</TD></TR></TBODY></TABLE>

Notice 3535[]A1 == 10+12 but we're not summing all A1 Code values because 5444[]A1 == 4 because it is a unique Product[]Code combination.

Obviously, I could use a Pivot Table, but since I'm on 2007 there's no repeating row function. Yes, I could do an intermediate step and create the "repeats" but I feel like there might be an easier/better/alternative way?

 

[Jerry Sullivan]

Here is one way...

Excel Workbook
	A	B	C
1	Product	Code	Total
2	3535	A1	10
3	3535	A1	12
4	3535	B3	5
5	5444	A1	4
6	5444	C3	8
7	5444	D5	9
8
9
10	Product	Code	Total
11	3535	A1	22
12	3535	B3	5
13	5444	A1	4
14	5444	C3	8
15	5444	D5	9
16
Sheet3

This can be simplified if you can have a helper column.

 

[markmzz]

Another way:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Product</td><td style="font-weight: bold;text-align: center;;">Code</td><td style="font-weight: bold;text-align: center;;">Total</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Product</td><td style="font-weight: bold;text-align: center;;">Code</td><td style="font-weight: bold;text-align: center;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #FFFF00;;">3535</td><td style="text-align: center;background-color: #FFFF00;;">A1</td><td style="text-align: center;background-color: #FFFF00;;">10</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">3535</td><td style="text-align: center;background-color: #FFFF00;;">A1</td><td style="text-align: center;background-color: #FFFF00;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #FFFF00;;">3535</td><td style="text-align: center;background-color: #FFFF00;;">A1</td><td style="text-align: center;background-color: #FFFF00;;">12</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #8DB4E3;;">3535</td><td style="text-align: center;background-color: #8DB4E3;;">B3</td><td style="text-align: center;background-color: #8DB4E3;;">18</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #8DB4E3;;">3535</td><td style="text-align: center;background-color: #8DB4E3;;">B3</td><td style="text-align: center;background-color: #8DB4E3;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">3535</td><td style="text-align: center;;">A2</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">5444</td><td style="text-align: center;;">A1</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="text-align: center;;">3535</td><td style="text-align: center;;">C2</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">5444</td><td style="text-align: center;;">C3</td><td style="text-align: center;;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;">5444</td><td style="text-align: center;;">A1</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5444</td><td style="text-align: center;;">D5</td><td style="text-align: center;;">9</td><td style="text-align: right;;"></td><td style="text-align: center;;">5444</td><td style="text-align: center;;">C3</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">3535</td><td style="text-align: center;;">A2</td><td style="text-align: center;;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;">5444</td><td style="text-align: center;;">D5</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">3535</td><td style="text-align: center;;">C2</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">5444</td><td style="text-align: center;;">B3</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #8DB4E3;;">3535</td><td style="text-align: center;background-color: #8DB4E3;;">B3</td><td style="text-align: center;background-color: #8DB4E3;;">13</td><td style="text-align: right;;"></td><td style="text-align: center;;">5444</td><td style="text-align: center;;">D1</td><td style="text-align: center;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">5444</td><td style="text-align: center;;">B3</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="text-align: center;;">5444</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">5444</td><td style="text-align: center;;">D1</td><td style="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></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">5444</td><td style="text-align: center;;">C1</td><td style="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></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$13=E2</font>),--(<font color="Red">B$2:B$13=F2</font>),C$2:C$13</font>)</td></tr></tbody></table></td></tr></table><br />
Note: to create the lists of columns E and F use Advanced Filter.

Markmzz

 

[Yahya]

another way :

Excel Workbook
	A	B	C	D	E	F	G	H
1	Product	Code	Total			Product	Code	Total
2	3535	A1	10			3535	A1	22
3	3535	A1	12			3535	B3	5
4	3535	B3	5			5444	A1	4
5	5444	A1	4			5444	C3	8
6	5444	C3	8			5444	D5	9
7	5444	D5	9
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

and if you are using Excel 2007 you can use this formula too
=SUMIFS($C$2:$C$7,$A$2:$A$7,F2,$B$2:$B$7,G2)