# Sum of unique values based on unique values in two other columns

#### frateg8r

##### Board Regular
I have a worksheet with 3 columns, as follows:
A: File Number
B: Master Number
C: Value \$

There are multiple occurrences of each file number for each master number; the \$ value for each file number is the same on each instance of the file number.

What I need to do is sum the \$ value of the master number, but only for each unique file number.
HTML:
``````COL A          COL B      COL C
10305	3910195070	\$5,266.92
10305	3910195070	\$5,266.92
10306	3910195070	\$5,266.92
10306	3910195070	\$5,266.92
10307	3910195070	\$14,846.13
10307	3910195070	\$14,846.13
10307	3910195070	\$14,846.13
10307	3910195070	\$14,846.13
10307	3910195070	\$14,846.13
10276	3910189075	\$2,880.40
10276	3910189075	\$2,880.40
10276	3910189075	\$2,880.40
10276	3910189075	\$2,880.40
10276	3910189075	\$2,880.40
10277	3910189075	\$280.42
10277	3910189075	\$280.42
10277	3910189075	\$280.42
10277	3910189075	\$280.42``````

In the sample data above, I need the values for the first instances of the file number in Col A, totaled by the master number in Col B.

For instance, the total for master number 3910189075 in Col B =
\$2880.40 + \$280.42 = \$3160.82
In this case, a formula that only extracts the first occurrence of a value in Col C is sufficient:
Code:
``=SUM(IF(FREQUENCY(C:C,C:C)>0,C:C)``

However, in the case with master number 3910195070, because there are TWO unique file numbers with the same \$ value, in sequence, the above formula only picks up the first occurrence of the \$ value, which of course skews the totals being calculated.

Can anyone help me with this formula to sum the \$ value based first on the master number, and secondly on the unique file number therein? I realize this is a rather convoluted question, but I'm not sure of the best way to show/phrase it.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### VoG

##### Legend
Try a Pivot Table

Excel Workbook
ABCDEFGH
1COL ACOL BCOL CSum of COL CColumn Labels
21030539101950705,266.92Row Labels39101890753910195070Grand Total
31030539101950705,266.92102761440214402
41030639101950705,266.92102771121.681121.68
51030639101950705,266.921030510533.8410533.84
610307391019507014,846.131030610533.8410533.84
710307391019507014,846.131030774230.6574230.65
810307391019507014,846.13Grand Total15523.6895298.33110822.01
910307391019507014,846.13
1010307391019507014,846.13
111027639101890752,880.40
121027639101890752,880.40
131027639101890752,880.40
141027639101890752,880.40
151027639101890752,880.40
16102773910189075280.42
17102773910189075280.42
18102773910189075280.42
19102773910189075280.42
Sheet4

#### Matty

##### Well-known Member
Here's a formula approach:

Sheet3

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 77px"><COL style="WIDTH: 105px"><COL style="WIDTH: 71px"><COL style="WIDTH: 19px"><COL style="WIDTH: 105px"><COL style="WIDTH: 71px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">File Number</TD><TD>Master Number</TD><TD>Value (\$)</TD><TD></TD><TD>Master Number</TD><TD>Value (\$)</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10305</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$5,266.92</TD><TD></TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$25,379.97</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10305</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$5,266.92</TD><TD></TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$3,160.82</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10306</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$5,266.92</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10306</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$5,266.92</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10307</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$14,846.13</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10307</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$14,846.13</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10307</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$14,846.13</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10307</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$14,846.13</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10307</TD><TD style="TEXT-ALIGN: right">3910195070</TD><TD style="TEXT-ALIGN: right">\$14,846.13</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10276</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$2,880.40</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10276</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$2,880.40</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10276</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$2,880.40</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10276</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$2,880.40</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10276</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$2,880.40</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10277</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$280.42</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10277</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$280.42</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10277</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$280.42</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial Unicode MS; FONT-SIZE: 10pt">10277</TD><TD style="TEXT-ALIGN: right">3910189075</TD><TD style="TEXT-ALIGN: right">\$280.42</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>{=SUM(IF(FREQUENCY(IF(B\$2:B\$19=E2,MATCH(A\$2:A\$19,A\$2:A\$19,0)),ROW(A\$2:A\$19)-ROW(A\$2)+1),C\$2:C\$19))}</TD></TR><TR><TD>F3</TD><TD>{=SUM(IF(FREQUENCY(IF(B\$2:B\$19=E3,MATCH(A\$2:A\$19,A\$2:A\$19,0)),ROW(A\$2:A\$19)-ROW(A\$2)+1),C\$2:C\$19))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Matty

#### frateg8r

##### Board Regular
Matty - it was PERFECT! Exactly what I was looking for. VoG - many thanks (pivots are a major Excel weak point for me.....)

#### Mike Szczesny

##### Active Member
Try this.
HTH,
Mike Szczesny

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">A</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">B</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">C</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">D</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">E</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">1</TD><TD>Item</TD><TD>Amount</TD><TD> </TD><TD>Unique Item</TD><TD>Amount</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">2</TD><TD>a</TD><TD style="TEXT-ALIGN: right">54</TD><TD style="TEXT-ALIGN: right">2</TD><TD>a</TD><TD style="TEXT-ALIGN: right">1046</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">3</TD><TD>b</TD><TD style="TEXT-ALIGN: right">55</TD><TD> </TD><TD>b</TD><TD style="TEXT-ALIGN: right">79</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">4</TD><TD>a</TD><TD style="TEXT-ALIGN: right">99</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">5</TD><TD>a</TD><TD style="TEXT-ALIGN: right">8</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">6</TD><TD>b</TD><TD style="TEXT-ALIGN: right">15</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">7</TD><TD>a</TD><TD style="TEXT-ALIGN: right">885</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">8</TD><TD>b</TD><TD style="TEXT-ALIGN: right">9</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>{=SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$8<>"",MATCH(\$A\$2:\$A\$8,\$A\$2:\$A\$8,0)),ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1),1))}</TD></TR><TR><TD>D2</TD><TD>{=INDEX(\$A\$2:\$A\$8,SMALL(IF(FREQUENCY(IF(\$A\$2:\$A\$8<>"",MATCH(\$A\$2:\$A\$8,\$A\$2:\$A\$8,0)),ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1),ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1),ROWS(\$D\$2:D2)))}</TD></TR><TR><TD>E2</TD><TD>{=SUM(IF(\$A\$2:\$A\$8=D2,\$B\$2:\$B\$8))}</TD></TR><TR><TD>D3</TD><TD>{=INDEX(\$A\$2:\$A\$8,SMALL(IF(FREQUENCY(IF(\$A\$2:\$A\$8<>"",MATCH(\$A\$2:\$A\$8,\$A\$2:\$A\$8,0)),ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1),ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1),ROWS(\$D\$2:D3)))}</TD></TR><TR><TD>E3</TD><TD>{=SUM(IF(\$A\$2:\$A\$8=D3,\$B\$2:\$B\$8))}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Replies
0
Views
848
Replies
8
Views
190
Replies
0
Views
396
Replies
0
Views
385
Replies
7
Views
449

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,576
Messages
5,832,577
Members
430,143
Latest member
Monyo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back