# How to Count Different Numbers

#### guybrown

##### Board Regular
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?

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### sulakvea

##### Well-known Member
why not use a pivot table

#### guybrown

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

Hello

Try.

Excel Workbook
AB
113
23
33
43
55
65
Sheet1

#### guybrown

##### Board Regular

Mint! Cheers Meldoc.

##### MrExcel MVP
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:

#### baitmaster

##### Well-known Member

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

##### MrExcel MVP
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

Last edited:

Replies
1
Views
93
Replies
5
Views
252
Replies
7
Views
176
Replies
4
Views
225
Replies
7
Views
1K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,855
Messages
5,766,781
Members
425,378
Latest member
kapoor2892

### 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.

### Which adblocker are you using?

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

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