Sum only unique values

[tb582]

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

 

[AlphaFrog]

Excel Workbook
	A	B
1	4.24	11480.71
2	2832.74
3	2832.74
4	8643.73
5	8643.73
...

#VALUE!

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

 

[energman58]

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]

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.

 

[Aladin Akyurek]

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]

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]

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]

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.

 

[Aladin Akyurek]

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

How about something like:

=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]

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)