Add only unique nos in a column

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Maybe

C D (headers in row 4)[TABLE="width: 110"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" width="82"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Numbers[/TD]
[TD="class: xl65, width: 82, bgcolor: transparent"]Sum Unique[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]150[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
hsandeep said:
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
Click to expand...

Control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(ISNUMBER(C5:C9999),MATCH(C5:C9999,C5:C9999,0)),
     ROW(C5:C9999)-ROW(C5)+1),C5:C9999))
 
Upvote 0
If you do not have empty spaces:
Excel 2010
ABC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Data[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: center"]140[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1


​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(A2:A10,A2:A10),A2:A10))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Adjust your ranges.
 
Upvote 0
Branco, Aladin & Mika: Thanks. The formula WORKS. Branco's formula is SUPERB.
 
Upvote 0
Aladin Akyurek said:
If you have non-numbers, empty cells or blanks, only the formula I suggested will work...
Click to expand...

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
 
Upvote 0
hsandeep said:
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
Click to expand...

[TABLE="width: 335"]
<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>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 146, bgcolor: transparent"]Unique Sum[/TD]
[TD="class: xl64, width: 174, bgcolor: transparent"]Unique Count[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]150[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]Unique List[/TD]
[TD="class: xl64, bgcolor: transparent"]Occurrence Frequency[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


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:
Rich (BB code): 
=ROW(Sheet1!$C$5:$C$9999)-ROW(Sheet1!$C$5)+1

E5, control+shift+enter, not just enter:
Rich (BB code): 
=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:
Rich (BB code): 
=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:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(C5:C9999<>"",MATCH(C5:C9999,C5:C9999,0)),Ivec),1))
E8, control+shift+enter and copy down:
Rich (BB code): 
=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...
Rich (BB code): 
=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:
Rich (BB code): 
=IF($E8="","",COUNTIF($C$5:$C$9999,$E8))
 
Upvote 0
E8, control+shift+enter and copy down:
Code: 
[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?
 
Upvote 0
hsandeep said:
E8, control+shift+enter and copy down:
Code: 
[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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
311
etaf
etaf
L
  • Solved
Combinations of 3 from 16 numbers pool with unique product
2
Replies
14
Views
623
libelouli
L
V
  • Question Question
Daily datewise calculation with colour formatting
Replies
7
Views
224
Cubist
Cubist
R
  • Question Question
VBA Question - VBA for validating value from multiple cells/column and fetching value another sheet's cell and pasting it another cell of sheet
Replies
2
Views
276
Kaper
Kaper
trishcollins
  • Question Question
Building a formula for a different named ranges that contain a portion of the formula
Replies
0
Views
196
trishcollins
trishcollins

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top