What does this formula "say"?

[AntBlabby8]

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!

 

[Aladin Akyurek]

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

You might want to start with the last one.

 

[AntBlabby8]

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!

 

[PaddyD]

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)

 

[Aladin Akyurek]

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.

 

[Tazguy37]

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.

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