# What does this formula "say"?

#### AntBlabby8

##### Board Regular
I need to count the number of unique occurrences in a column that is more than five hundred rows. I often do this by creating a simple pivot table the rows of which I count for my #, but as easy as it is, it's still a bit more work than seems necessary just to come up with a number. I have now found this formula for doing it, in a zillion places on the Net, including here on Mr. Excel. The problem is, I don't understand it. What exactly is this saying...The part I really don't get is the Apersand in the Countif. It must be something I haven't yet learned. Can someone repeat this formula in English, so to speak?

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

Thanks, as usual. My entire non-basic knowledge of Excel comes from this website I think!

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
AntBlabby8 said:
I need to count the number of unique occurrences in a column that is more than five hundred rows. I often do this by creating a simple pivot table the rows of which I count for my #, but as easy as it is, it's still a bit more work than seems necessary just to come up with a number. I have now found this formula for doing it, in a zillion places on the Net, including here on Mr. Excel. The problem is, I don't understand it. What exactly is this saying...The part I really don't get is the Apersand in the Countif. It must be something I haven't yet learned. Can someone repeat this formula in English, so to speak?

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

Thanks, as usual. My entire non-basic knowledge of Excel comes from this website I think!

Not sure if they are "in English", here a few links containing my attempts at explanation:

http://www.mrexcel.com/board2/viewtopic.php?t=37550&highlight=token

http://www.mrexcel.com/board2/viewtopic.php?t=17071&highlight=hager

http://www.mrexcel.com/board2/viewtopic.php?t=17071&highlight=hager

http://www.mrexcel.com/board2/viewtopic.php?t=73502&highlight=grove

Done! That answered ALL my questions. Now I think Microsoft should invent a new function called "CountU" which would count unique occurrences of a value. That is: =CountU(A1:C15) for example and you'd get the number of cells with unique values. Until then, thanks for your help. That was the "English" I meant!

Microsoft hasn't but it does exist - countdiff() is part of the morefunc addin (see recommended addins & links thread at the top of the board)

AntBlabby8 said:
Done! That answered ALL my questions. Now I think Microsoft should invent a new function called "CountU" which would count unique occurrences of a value. That is: =CountU(A1:C15) for example and you'd get the number of cells with unique values. Until then, thanks for your help. That was the "English" I meant!

Longre has one in his morefunc.xll add-in with desirable properties for (conditional) distinct count MS could "buy in". It is COUNTDIFF() in case you're interested.

AntBlabby8 said:
Now I think Microsoft should invent a new function called "CountU" which would count unique occurrences of a value. That is: =CountU(A1:C15) for example and you'd get the number of cells with unique values.

They probably won't, as it's now been done with Formulas, UDFs, etc. Nice idea, though.

Thanks for those links! This one, in particular was most helpful. I never really "got" that construction until now.

Replies
1
Views
117
Replies
6
Views
199
Replies
11
Views
361
Replies
9
Views
353
Replies
1
Views
163

1,203,082
Messages
6,053,420
Members
444,662
Latest member
AaronPMH

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