# Sum only unique values

#### tb582

##### New Member
I have number values in G2:G50 with some blanks included - what I'm looking for is a formula that will add all the values together but will only add values that exist more than once one time.

4.24
2832.74
2832.74
8643.73
8643.73

the only things that should get added at 4.24, 2832.74 and 8643.73

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### AlphaFrog

##### MrExcel MVP
Excel Workbook
AB
14.2411480.71
22832.74
32832.74
48643.73
58643.73
...
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

#### energman58

##### Well-known Member
I dont think there is an easy way of doing this with a formula.

You can extract the unique values using either data - advanced (2007+) or data - filter - advanced filter (<=2003) and then add them easily enough. You could automate this with a macro if you want.

The alternative is to use a helper column-

In H2 put a formula like this:

=IF(COUNTIF(G1:\$G\$50,G1)>1,0,G1)

And copy it down to H50

This will return a column with the value opposite the last time a number appears in your data and zero all the other times you then add the column to get your total.

#### Marcelo Branco

##### MrExcel MVP
Hi,

Maybe this

G H

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=127><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2635053 class=xl65 height=20 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>4,24

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>11480,71</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>2832,74</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>2832,74</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>8643,73</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>8643,73

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2635053 class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>8643,73</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>4,24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>2832,74</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR></TBODY></TABLE>

Formula in H2
=SUM(IF(G2:G50<>"",G2:G50/COUNTIF(G2:G50,G2:G50)))
confirmed with Ctrl+Shift+Enter, not just Enter

HTH

M.

##### MrExcel MVP
I have number values in G2:G50 with some blanks included - what I'm looking for is a formula that will add all the values together but will only add values that exist more than once one time.

4.24
2832.74
2832.74
8643.73
8643.73

the only things that should get added at 4.24, 2832.74 and 8643.73

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A6<>"",MATCH(A2:A6,A2:A6,0)),ROW(A2:A6)-ROW(A2)+1),A2:A6))

#### tb582

##### New Member
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A6<>"",MATCH(A2:A6,A2:A6,0)),ROW(A2:A6)-ROW(A2)+1),A2:A6))

Worked like a charm - thanks! one more question is there a way to tell excel that A2:A6 is in GB but yet the SUM should be calculated in TB?

#### tb582

##### New Member
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A6<>"",MATCH(A2:A6,A2:A6,0)),ROW(A2:A6)-ROW(A2)+1),A2:A6))

Worked like a charm - thanks!

actually I don't know if this is working as expect: =SUM(IF(FREQUENCY(IF(G2:G47<>"",MATCH(G2:G47,G2:G47,0)),ROW(G2:G47)-ROW(G2)+1),G2:G47)) the SUM is much larger than if I just add all the numbers up manually.

#### AlphaFrog

##### MrExcel MVP
Do you have any so-called duplicates that may have slightly different decimal values e.g.

8643.7301
8643.7302

If you are displaying only two decimal places, these would "look" like duplicates but all the formulas above would consider them unique.

##### MrExcel MVP
actually I don't know if this is working as expect: =SUM(IF(FREQUENCY(IF(G2:G47<>"",MATCH(G2:G47,G2:G47,0)),ROW(G2:G47)-ROW(G2)+1),G2:G47)) the SUM is much larger than if I just add all the numbers up manually.

AlphaFrog rightly points out that the decimals might throw us off...

=SUM(IF(FREQUENCY(IF(G2:G47<>"",MATCH(ROUND(G2:G47,3),ROUND(G2:G47,3),0)),ROW(G2:G47)-ROW(G2)+1),ROUND(G2:G47,3)))

still to be confirmed with control+shift+enter?

#### T. Valko

##### Well-known Member
I have number values in G2:G50 with some blanks included - what I'm looking for is a formula that will add all the values together but will only add values that exist more than once one time.

4.24
2832.74
2832.74
8643.73
8643.73

the only things that should get added at 4.24, 2832.74 and 8643.73
Try this...

=SUMPRODUCT(1/COUNTIF(G2:G50,G2:G50&""),G2:G50)

Replies
17
Views
124
Replies
3
Views
321
Replies
12
Views
278
Replies
18
Views
501
Replies
1
Views
150

1,191,032
Messages
5,984,238
Members
439,879
Latest member
KingGoulash

### 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