Count unique items?

[theta]

Hi All

Bit stuck on a formula. Normally to count unique items in a range (say A1:A10) i would use the array formula =SUM(1/COUNTIF(A1:A10,A1:A10)

This works great, but now I have a criteria to use. I would like it so that it only takes account of cells where B1:B10 = 1


GROUP1.......0
GROUP1.......0
GROUP1.......1
GROUP2.......0
GROUP2.......0
.............1
GROUP4.......1
GROUP5.......1
GROUP7.......1
GROUP7.......1


So in this instance the desired answer would be 4 (counted GROUP1, GROUP4, GROUP5, GROUP7)

The blank group without a name was ignored (as it normally is using the SUM(1/COUNTIF method)

Any ideas please ?

 

[theta]

Anyone - guessing may use SUMPRODUCT or the FREQUENCY function... ?

 

[venkat1926]

I used Aladin Akyurek's formula
http://www.mrexcel.com/forum/showthread.php?t=21812

the data is like this from row no. 1


<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">GROUP1</td> <td style="width:48pt" align="right" width="64">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">GROUP1</td> <td align="right">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">GROUP1</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">GROUP2</td> <td align="right">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">GROUP2</td> <td align="right">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">......</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">GROUP4</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">GROUP5</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">GROUP7</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">GROUP7</td> <td align="right">1</td> </tr> </tbody></table>

InC1 copy this formula

=IF(B1<>0,A1,"")
copy C1 down

now in an empty cell copy this formula

=SUM(IF(LEN(C1:C100),1/COUNTIF(C1:C100,C1:C100)))-1
that 1 is one blank in A1 ti A10
INVOKE THIS FORMULA WITH CONTROL SHIFT ENTER


you can tweak little more. shall look into it

 

[theta]

That feels like a bit of a hack...i wanted to avoid using helper columns (sorry should have said) and instead use array or logic formula to exclude those cells...

Any ideas?

 

[rafaelspeixoto]

You can use this formula:
=SUM(IF(FREQUENCY(IF(B1:B10>0;IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);"");"");IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);""))>0;1))

It's also an array formula, so ctrl+shift+enter

 

[Aladin Akyurek]

Hi All

Bit stuck on a formula. Normally to count unique items in a range (say A1:A10) i would use the array formula =SUM(1/COUNTIF(A1:A10,A1:A10)

This works great, but now I have a criteria to use. I would like it so that it only takes account of cells where B1:B10 = 1


GROUP1.......0
GROUP1.......0
GROUP1.......1
GROUP2.......0
GROUP2.......0
.............1
GROUP4.......1
GROUP5.......1
GROUP7.......1
GROUP7.......1


So in this instance the desired answer would be 4 (counted GROUP1, GROUP4, GROUP5, GROUP7)

The blank group without a name was ignored (as it normally is using the SUM(1/COUNTIF method)

Any ideas please ?

That method is less convenient for conditional unique count. Try the following instead...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$1:$A$10<>"",IF($B$1:$B$10=1,
  MATCH("~"&$A$1:$A$10,$A$1:$A$10&"",0))),
   ROW($A$1:$A$10)-ROW($A$1)+1),1)

If there are no special meaning chars around entries in A1:A10, the "~"& and &"" bits can safely be removed from the foregoing formula.