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

frateg8r · Aug 8, 2010

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.

VoG · Aug 8, 2010

Try a Pivot Table

Excel Workbook

A

B

C

D

E

F

G

H

1

COL A

COL B

COL C

Sum of COL C

Column Labels

2

10305

3910195070

5,266.92

Row Labels

3910189075

3910195070

Grand Total

3

10305

3910195070

5,266.92

10276

14402

4

10306

3910195070

5,266.92

10277

1121.68

5

10306

3910195070

5,266.92

10305

10533.84

6

10307

3910195070

14,846.13

10306

10533.84

7

10307

3910195070

14,846.13

10307

74230.65

8

10307

3910195070

14,846.13

Grand Total

15523.68

95298.33

110822.01

9

10307

3910195070

14,846.13

10

10307

3910195070

14,846.13

11

10276

3910189075

2,880.40

12

10276

3910189075

2,880.40

13

10276

3910189075

2,880.40

14

10276

3910189075

2,880.40

15

10276

3910189075

2,880.40

16

10277

3910189075

280.42

17

10277

3910189075

280.42

18

10277

3910189075

280.42

19

10277

3910189075

280.42

Sheet4

Matty · Aug 8, 2010

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 · Aug 18, 2010

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

Mike Szczesny · Aug 20, 2010

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>

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

frateg8r

Board Regular

Excel Facts

VoG

Legend

Matty

Well-known Member

frateg8r

Board Regular

Mike Szczesny

Active Member

Similar threads

Forum statistics

Share this page

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

frateg8r

Board Regular

Excel Facts

VoG

Legend

Matty

Well-known Member

frateg8r

Board Regular

Mike Szczesny

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock