Add only unique nos in a column

[hsandeep]

in column C5:C9999, i have values appearing as

40, 50, 60, 50, 60, 40, 40
I want total of 'unique appearance' no.
How to accomplish?
Answer for above is: 40+50+60=150

 

[Marcelo Branco]

Maybe

C D (headers in row 4)

Numbers	Sum Unique
40	150
50
60
50
60
40
40

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" width="82"> <tbody>
</tbody>

Formula in D5
=SUMPRODUCT(--(MATCH($C$5:$C$11,$C$5:$C$11,0)=ROW($C$5:$C$11)-ROW($C$5)+1),$C$5:C11)

M.

 

[Aladin Akyurek]

in column C5:C9999, i have values appearing as

40, 50, 60, 50, 60, 40, 40
I want total of 'unique appearance' no.
How to accomplish?
Answer for above is: 40+50+60=150

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(C5:C9999),MATCH(C5:C9999,C5:C9999,0)),
     ROW(C5:C9999)-ROW(C5)+1),C5:C9999))

 

[Robert Mika]

If you do not have empty spaces:
Excel 2010

	A	B	C
1	Data		Result
2	20		140
3	20
4	20
5	40
6	40
7	40
8	40
9	80
10	80

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1


​

Array Formulas Cell Formula C2 {=SUM(IF(FREQUENCY(A2:A10,A2:A10),A2:A10))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Adjust your ranges.

 

[hsandeep]

Branco, Aladin & Mika: Thanks. The formula WORKS. Branco's formula is SUPERB.

 

[Aladin Akyurek]

Branco, Aladin & Mika: Thanks. The formula WORKS. Branco's formula is SUPERB.

If you have non-numbers, empty cells or blanks, only the formula I suggested will work...

 

[hsandeep]

If you have non-numbers, empty cells or blanks, only the formula I suggested will work...

Aladin,
Yes, I have non-numbers, nulls or blanks. So I am using your formula.
Please, Can I also get the no. of 'unique appearances'?
for 40, 50, 60, 50, 60, 40, 40
Answer=3
Part B) Also how to accomplish:
40 3
50 2
60 2

 

[Aladin Akyurek]

Aladin,
Yes, I have non-numbers, nulls or blanks. So I am using your formula.
Please, Can I also get the no. of 'unique appearances'?
for 40, 50, 60, 50, 60, 40, 40
Answer=3
Part B) Also how to accomplish:
40 3
50 2
60 2

40		Unique Sum	Unique Count
50		150	3
60		Unique List	Occurrence Frequency
50		40	3
		50	2
60		60	2
40
40

<colgroup><col style="width: 48pt;" span="2" width="64"> <col style="width: 109pt; mso-width-source: userset; mso-width-alt: 5176;" width="146"> <col style="width: 130pt; mso-width-source: userset; mso-width-alt: 6172;" width="174"> <tbody>
</tbody>

The data is C5:C9999 on Sheet1 (Adjust to suit if necessary).

Define Ivec (of Integer Vector) using Insert | Name | Define or Formulas | Name Manager as referring to:

=ROW(Sheet1!$C$5:$C$9999)-ROW(Sheet1!$C$5)+1

E5, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(C5:C9999),MATCH(C5:C9999,C5:C9999,0)),Ivec),C5:C9999))

This sums the distinct (unique) numbers in the target range.
F5, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(C5:C9999),MATCH(C5:C9999,C5:C9999,0)),Ivec),1))

If we want to count any distinct value, numeric or text, we change the ISNUMBER bit:

=SUM(IF(FREQUENCY(IF(C5:C9999<>"",MATCH(C5:C9999,C5:C9999,0)),Ivec),1))

E8, control+shift+enter and copy down:

=IF(ROWS($E$3:E3)<=$F$6,INDEX($C$5:$C$9999,
  SMALL(IF(FREQUENCY(IF(ISNUMBER($C$5:$C$9999),MATCH($C$5:$C$9999,$C$5:$C$9999,0)),
   Ivec),Ivec),ROWS($E$3:E3))),"")

If we want to list any distinct value, numeric or text, we replace the ISNUMBER bit...

=IF(ROWS($E$3:E3)<=$F$6,INDEX($C$5:$C$9999,
  SMALL(IF(FREQUENCY(IF($C$5:$C$9999<>"",MATCH($C$5:$C$9999,$C$5:$C$9999,0)),
   Ivec),Ivec),ROWS($E$3:E3))),"")

F8, just enter and copy down:

=IF($E8="","",COUNTIF($C$5:$C$9999,$E8))

 

[hsandeep]

E8, control+shift+enter and copy down:

[SIZE=2][FONT=lucida console]=IF(ROWS($E$3:E3)<=$F$6,INDEX($C$5:$C$9999,
  SMALL(IF(FREQUENCY(IF(ISNUMBER($C$5:$C$9999),MATCH($C$5:$C$9999,$C$5:$C$9999,0)),
   Ivec),Ivec),ROWS($E$3:E3))),"")[/FONT][/SIZE]

I am trying to copy this formula using CTRL + C but it is coming in 3 rows.  How to copy the formula?

 

[Aladin Akyurek]

E8, control+shift+enter and copy down:

[SIZE=2][FONT=lucida console]=IF(ROWS($E$3:E3)<=$F$6,INDEX($C$5:$C$9999,
  SMALL(IF(FREQUENCY(IF(ISNUMBER($C$5:$C$9999),MATCH($C$5:$C$9999,$C$5:$C$9999,0)),
   Ivec),Ivec),ROWS($E$3:E3))),"")[/FONT][/SIZE]

I am trying to copy this formula using CTRL + C but it is coming in 3 rows.  How to copy the formula?[/QUOTE]

After you type the formula in E8, apply immediately control+shift+enter (CSE). Then drag it down as you would a non-CSE formula.