How to Count Different Numbers

[guybrown]

Hi all!

Struggling on something I thought would be simple again!

A1 1
A2 3
A3 3
A4 3
A5 5
A6 5

There are 3 different numbers being 1, 3 and 5. How do I get a result of 3?

Cheers in advance!

 

[sulakvea]

why not use a pivot table

 

[guybrown]

I'd rather use a formula. I'm afraid of Pivot Tables :/

 

[meldoc]

Hello

Try.

Excel Workbook
	A	B
1	1	3
2	3
3	3
4	3
5	5
6	5
Sheet1

 

[guybrown]

Mint! Cheers Meldoc.

 

[Aladin Akyurek]

That should be:

=SUMPRODUCT((A1:A6<>"")/COUNTIF(A1:A6,A1:A6&""))

unless you want to include empty/blank cell as a distinct entry.

Faster...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A1:A6<>"",MATCH("~"&A1:A6,A1:A6&"",0)),ROW(A1:A6)-ROW(A1)+1),1))

See:

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[baitmaster]

interesting formula Meldoc, but could you please explain how this is calculating?

I notice it treats empty cells as a unique value too, a little warning to the unwary...

thanks

 

[meldoc]

Hello Baitmaster

interesting formula Meldoc, but could you please explain how this is calculating?

For a better explanation than I can Give, see the link.

http://www.youtube.com/user/ExcelIsFun#p/search/1/XPb0S3Rbdf4

Aladin thanks for the catch, the wrong formula imperfectly remembered.

 

[Aladin Akyurek]

interesting formula Meldoc, but could you please explain how this is calculating?

I notice it treats empty cells as a unique value too, a little warning to the unwary...

thanks

Here some links:

http://www.mrexcel.com/board2/viewto...ighlight=token
http://www.mrexcel.com/board2/viewto...ighlight=hager
http://www.mrexcel.com/forum/showthread.php?t=70835