LinkBack URL
About LinkBacks
I use the following formula to count the number of unique values:
={SUM(1/COUNTIF(Range;Range))}
This one works as far as there aren’t any blank cells. The result is then: #DIV/0!
How could I construct the formula to ignore blank cells?
Saturn
I use the following formula to count the number of unique values:
={SUM(1/COUNTIF(Range;Range))}
This one works as far as there aren’t any blank cells. The result is then: #DIV/0!
How could I construct the formula to ignore blank cells?
Saturn
Last edited by Saturn; 04-09-2012 at 10:19 AM.
Hi
Pls, give a try to this.
=SUM(IF(FREQUENCY(A1:A10;A1:A10)=1;FREQUENCY(A1:A10;A1:A10)))
Replace, semi-colons, to comma, if you have to do it.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
I'am afraid this doesn't work.
The answer is 0, with and without blank cells.
Hello Saturn,
=IFERROR(SUM(1/COUNTIF(Range,Value)),"")
Should do it.
Thanks,
RVASQUEZ
Take a look to the example, pls.
Hello Fotis 1991
Thanks for your answer, but the result should be 5 and not 3 (5, 7, 10, 20, 25). Could you look into this?
(rvasquez sugestion is not working. IFERROR gives a blank cell if one of the cells is blank... )
Count 3, because it counts the values, that appears, only one time(5.7.25)
Just now the only way, that i can thing for what you really ask for, is using a helper(and hidden)column)
So in B1, put this formula and copy down.
Then, this formula, will gives you, 5!PHP Code:=COUNTIF($A1:$A$10;A1)
PHP Code:=COUNTIF(B1:B10;"=1")
Correction on Fotis's formula
=COUNTIF($A$1:$A1;A1) dragged down
Edit: Nevermind. I see Folis just did it the reverse. Either way will work. My mistake.
Last edited by ChemistB; 04-09-2012 at 10:16 AM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Great, this works. A creative solution!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks