Count Unique records
Hi guys,
So I have this sheet with 5 columns, column B contains codes like 040401, 050401, 060604 etc.. This column runs from B2 to B89(still growing).
And I want to have a formula to give me an idea on how many unique records I have in column B.
Thank you in advance!
Here, use this:
Formula:
=SUMPRODUCT(--(COUNTIF(B2:B10000,B2:B10000)=1))
Never use Merged Cells in Excel
Perhapsentered as an array formula with Ctrl+Shift+Enter
=SUM(IF(COUNTIF(B2:B89,B2:B89)=1,1,0))
No need for IF
Formula:=SUM(--(COUNTIF(B1:B100;B1:B100)=1))
CSE
With this I get a result of '1', so that's not working as the unique results is definitely higher than 70.
Maybe good to know that the column/codes are 'text' not 'numbers'
If you talk aobut 2nd formula it must be entered with Contrl+shift+enter (not just enter)
Does not work either.. I have a mac and tried everything (is supposed to be alt+control+enter)
Doesn't matter. Works for text too.
result: 3 a b v c c 5 2 2 7 a a b
Well it doesn't work in this column..
If I press enter to insert the formula it gives an error and selects B89..
Enter formula in the formula bar
Leave the insertion point there
Click Ctrl+Shift+Enter all at once - Formula is now between curly braces.
Same procedure each time you edit the formula
Adding braces manually will not work
I don't understand why don't you use formula in post that can be normally entered?!?
Okay this did work in the end, thank you for that!
The only way I get now that it completely excludes the double values (so for instance the code 123 is present a couple of times in the column, then I want it to be counted as 1, now it doesn't count it at all..).
How can I do that?
Try this array formula**:
=SUM(IF(FREQUENCY(IF(B2:B100<>"",MATCH(B2:B100,B2:B100,0)),ROW(B2:B100)-ROW(B2)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Maybe try this:
Formula:=SUM(IF(FREQUENCY(B:B,B:B)>0,1))
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Okay Thanks, I wanted to try it but as strange as it may sound: I can't find the 'Frecuency' function in my Dutch version of Excel 2011 for Mac. Because it is Dutch all the functions are also Dutch, but I cannot find the equivalent of 'frequency'.. very strange...
See if this helps:
http://www.piuha.fi/excel-function-n...ish-dutch.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks