Count cell only only

[MR8]

Hi guys,

I did a search and wasn't able to find anything about a formula to count the record only once.

Is there a Count Formula to count the information once.

E.g

Col A
Coke
Coke
Rum
Brandy
Rum
Vodka
Vodka

So at the moment when I do a count formula it returns the value as 7, when I want it to return 5. As I want coke and Vodka to be counted only once.

Thanks

 

[c_m]

wouldnt your answer be 4 for the example your have posted?

<title>Excel Jeanie HTML</title><table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>coke</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>coke</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>rum</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>brandy</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>rum</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>vodka</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>vodka</td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B1</td> <td>{=SUM(1/COUNTIF(A1:A7,A1:A7))}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>

 

[Aladin Akyurek]

Hi guys,

I did a search and wasn't able to find anything about a formula to count the record only once.

Is there a Count Formula to count the information once.

E.g

Col A
Coke
Coke
Rum
Brandy
Rum
Vodka
Vodka

So at the moment when I do a count formula it returns the value as 7, when I want it to return 5. As I want coke and Vodka to be counted only once.

Thanks

It's called unique or distinct items count.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A8<>"",MATCH("~"&A2:A8,A2:A8&"",0)),ROW(A2:A8)-ROW(A2)+1),1))

 

[shemayisroel]

Aladin,

Re your solution I also use the same for extracting a unique/distinct count. Would that solution be a more expensive solution than c_m's?

Also are the any advantages of your solution compared to c_m's?

 

[c_m]

one advantage of Aladin's solution that I could think of is: if any cells in the range are blank, Aladin's suggestion would still work while mine wont.

 

[mikerickson]

This modification will protect c_m's formula

=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7&""))

but it counts empty cells as an occurance, you may want to use

=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7&""))-SIGN(COUNTIF(A1:A7,""))

addendem: both formulas think that the text entry "10" is the same as the number 10.

 

[Aladin Akyurek]

There is enough on this type of formulas...


http://www.mrexcel.com/board2/viewto...ighlight=token
http://www.mrexcel.com/board2/viewto...ighlight=hager
http://www.mrexcel.com/board2/viewto...ighlight=grove

http://www.mrexcel.com/forum/showthread.php?t=292473

The latter provides a performance comparison.

 

[shemayisroel]

WOW

Thanks c_m & mikerickson.

Aladin thanks for that I really like this!!!

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[MR8]

Thanks for your helps guys... can someone explain what the ctrl+shift+enter dose?

 

[jeffreybrown]

Maybe this could help...

http://www.mrexcel.com/articles/CSE-array-formulas-excel.php