Can I count how many numbers in a list, excluding duplicates

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Feb 13, 2007

• #2

THEBAGSTER

Welcome to the Mr Excel board!

Try Domenic's solution from this thread. I think you will find you only have 11 unique entries in your list.
http://www.mrexcel.com/board2/viewtopic.php?t=171895

 

Upvote 0

Lewiy

Well-known Member

Joined

Jan 5, 2007

Messages

4,284

• Feb 13, 2007

• #3

This is a little crude but if you assume that your data is in column A and in ascending order, then in cell B1 put "1" and in B2 put "=IF(A2=A1,0,1)" then copy this down the rest of column B then sum column B.

 

Upvote 0

T

THEBAGSTER

New Member

Joined

Feb 13, 2007

Messages

2

• Feb 13, 2007

• #4

thanks Peter,

I deliberately miscounted to make sure people were alert!!!

That works perfectly...

The Bagster
sunny England.

 

Upvote 0

arthurbr

Well-known Member

Joined

Dec 8, 2006

Messages

2,725

Office Version

1. 365

Platform

1. Windows

• Feb 13, 2007

• #5

This will work also ( entered as array formula)
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

and YES Peter there are only 11

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Feb 13, 2007

• #6

I deliberately miscounted to make sure people were alert!!!

Click to expand...

Good one!!

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Feb 13, 2007

• #7

This will work also ( entered as array formula)
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

and YES Peter there are only 11

Click to expand...

I can't see that this will count the unique entries (that is, produce the result 11 in this case). Also, I can't see what entering it as an array formula achieves. If entered as an ordinary formula in, say, column B and copied down it will list the unique entries, but not count them.

 

Upvote 0

arthurbr

Well-known Member

Joined

Dec 8, 2006

Messages

2,725

Office Version

1. 365

Platform

1. Windows

• Feb 13, 2007

• #8

Yes you're right, this formula only lits the unique entries
This one will count them :=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))
(only numbers and blank cells are counted)

 

Upvote 0