Counting unique number

madhatrs26 · Apr 6, 2015

I have an issue with trying to find a formula that can count the unique number in a list. Here is the data so referencing column A and counting the unique number in column B.

USER_ID	LABEL
axr681	0321423846
axr681	0321423846
axr681	0321423859
mxf358	0321423850
mxf358	0321423852
mxf358	0321423852
mxf358	0321423852
mxf358	0321423867
rxv829	0321423851
rxv829	0321423851
rxv829	0321423864
tmj255	0321423849
tmj255	0321423849
tmj255	0321423849
tmj255	0321423861

Please assist with this problem.

Thank you much,

madhatter

Caribeiro77 · Apr 6, 2015

Welcome to the board, not shure if i understood what you need but try:

Book1

A

B

1

USER_ID

LABEL

2

axr681

321423846

3

axr681

321423846

4

axr681

321423859

5

mxf358

321423850

6

mxf358

321423852

7

mxf358

321423852

8

mxf358

321423852

9

mxf358

321423867

10

rxv829

321423851

11

rxv829

321423851

12

rxv829

321423864

13

tmj255

321423849

14

tmj255

321423849

15

tmj255

321423849

16

tmj255

321423861

17

18

19

9

Sheet3

Cell Formulas
Range	Formula
A19	{=SUM(1/COUNTIFS($A$2:$A$16,$A$2:$A$16,$B$2:$B$16,B2:B16))}
Press CTRL+SHIFT+ENTER to enter array formulas.

lrobbo314 · Apr 6, 2015

Assuming that your data is in column B, this should work.

Code:

=SUM(1/COUNTIF(B1:B15,B1:B15))

Hit Ctrl + Shift + Enter

madhatrs26 · Apr 6, 2015

I need to get a count of column B which is a label number just one criteria and using column A as a reference. Example would be USER_ID axr681 should have a count of 2 LABELS because in the example B2 and B3 are the same label number.

krykm · Apr 6, 2015

Row\Col	A	B	C	D	E
1	USER_ID	LABEL
2	axr681	321423846		axr681	2
3	axr681	321423846		mxf358	3
4	axr681	321423859		rxv829	2
5	mxf358	321423850		tmj255	2
6	mxf358	321423852
7	mxf358	321423852
8	mxf358	321423852
9	mxf358	321423867
10	rxv829	321423851
11	rxv829	321423851
12	rxv829	321423864
13	tmj255	321423849
14	tmj255	321423849
15	tmj255	321423849
16	tmj255	321423861

Row\Col	E
2	=SUM(IF(FREQUENCY(IF($A$2:$A$16=D2,$B$2:$B$16),IF($A$2:$A$16=D2,$B$2:$B$16))>0,1))

Ctrl+Shift+Enter

Hope helps

Aladin Akyurek · Apr 6, 2015

madhatrs26 said:
I need to get a count of column B which is a label number just one criteria and using column A as a reference. Example would be USER_ID axr681 should have a count of 2 LABELS because in the example B2 and B3 are the same label number.

Row\Col	A	B	C
1	USER_ID	LABEL	COUNT
2	axr681	321423846	2
3	axr681	321423846	2
4	axr681	321423859	2
5	mxf358	321423850	3
6	mxf358	321423852	3
7	mxf358	321423852	3
8	mxf358	321423852	3
9	mxf358	321423867	3
10	rxv829	321423851	2
11	rxv829	321423851	2
12	rxv829	321423864	2
13	tmj255	321423849	2
14	tmj255	321423849	2
15	tmj255	321423849	2
16	tmj255	321423861	2

C2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$16=A2,$B$2:$B$16),$B$2:$B$16),1))

madhatrs26 · Apr 6, 2015

krykm your answer works only issue is that the leading zero's are required how can this formula work with a leading zero?

Thanks,

Madahtter

Aladin Akyurek · Apr 6, 2015

Aladin Akyurek said:
Row\Col
A
B
C
1
USER_ID LABEL COUNT
2
axr681
321423846
2
3
axr681
321423846
2
4
axr681
321423859
2
5
mxf358
321423850
3
6
mxf358
321423852
3
7
mxf358
321423852
3
8
mxf358
321423852
3
9
mxf358
321423867
3
10
rxv829
321423851
2
11
rxv829
321423851
2
12
rxv829
321423864
2
13
tmj255
321423849
2
14
tmj255
321423849
2
15
tmj255
321423849
2
16
tmj255
321423861
2

C2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$16=A2,$B$2:$B$16),$B$2:$B$16),1))

madhatrs26 said:
krykm your answer works only issue is that the leading zero's are required how can this formula work with a leading zero?

Thanks,

Madahtter

If your labels are text numbers (not true numbers), control+shift+enter, not just enter, and copy down:

Rich (BB code):


=SUM(IF(FREQUENCY(IF($A$2:$A$16=A2,IF($B$2:$B$16<>"",
   MATCH($B$2:$B$16,$B$2:$B$6,0))),ROW($B$2:$B$6)-ROW($B$2)+1),1))

madhatrs26 · Apr 6, 2015

Thank you much Aladin Akyurek for the formula this is exactly what I was looking for thank you everyone else as well for your input.

Madhatter

Aladin Akyurek · Apr 6, 2015

madhatrs26 said:
Thank you much Aladin Akyurek for the formula this is exactly what I was looking for thank you everyone else as well for your input.

Madhatter

You are welcome.

Counting unique number

madhatrs26

New Member

Excel Facts

Caribeiro77

Well-known Member

lrobbo314

Well-known Member

madhatrs26

New Member

krykm

Board Regular

Aladin Akyurek

MrExcel MVP

madhatrs26

New Member

Aladin Akyurek

MrExcel MVP

madhatrs26

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Counting unique number

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Board Regular

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

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