Counting Unique Values

[Guest]

I have my data like so:

ColA ColB
1 A
1 B
2 A
2 B
1 C
1 D

Is there a way yo count the number of unique values in ColB if ColA value
equals 1.

So in the above, the answer would be 4.

Thank you in advance.

 

[Bob Phillips]

=SUMPRODUCT((A1:A20=1)/COUNTIF(B1:B20,B1:B20&""))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RagDyer]

And ... if Column B should happen to contain blanks, you could try this:

=SUMPRODUCT((A1:A20=1)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<>""))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF((A1:A6=1)*(B1:B6<>""),MATCH(B1:B6,B1:B6,0)),ROW(INDE
X(B1:B6,0,0))-ROW(B1)+1)>0,1,0))

OR

=COUNT(1/FREQUENCY(IF((A1:A6=1)*(B1:B6<>""),MATCH(B1:B6,B1:B6,0)),ROW(IND
EX(B1:B6,0,0))-ROW(B1)+1))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

 

[Aladin Akyurek]

I have my data like so:

ColA ColB
1 A
1 B
2 A
2 B
1 C
1 D

Is there a way yo count the number of unique values in ColB if ColA value
equals 1.

So in the above, the answer would be 4.

Thank you in advance.

If you have Longre's morefunc.xll add-in:

=COUNTDIFF(IF($A$2:$A$7=1,$B$2:$B$7,0),FALSE,0)

which you need to confirm with control+shift+enter.

Or:

=COUNT(1/FREQUENCY(IF((A2:A7=1)*(B2:B7<>""),MATCH(B2:B7,B2:B7,0)),ROW(INDEX(B2:B7,0,0))-ROW(B2)+1))

which must be confirmed with control+shift+enter.
--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Aladin Akyurek]

=SUMPRODUCT((A1:A20=1)/COUNTIF(B1:B20,B1:B20&""))

Did you test that? Here is a sample:

{1,"A";0,"B";2,"A";2,"B";1,0;1,"D"}

where 0 stands for empty cells. Formula blanks [ ="" ] would have the
same effect.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Bob Phillips]

Did you test that? Here is a sample:

Of course I did, and it worked with data as presented. I didn't test with
gaps in column B, but that is simply corrected if necessary.

 

[Guest]

Thanks so much for this string, it was exactly the info I needed...

But - I tried this with my data (4000 rows, one column with values "Include"
and "Exclude" that is the criteria for counting the unique text values in the
second column), and ended up with a formula result 1043.4907. Do I round up
or down???

=SUMPRODUCT((A2:A3964="Include)/COUNTIF(D23964, D23964&""))

I don't understand how the formula works or I would troubleshoot it myself

 

[Aladin Akyurek]

Two options...

{a) If you have the latest version of Longre's morefunc.xll add-in:

=COUNTDIFF(IF(A2:A15="Include",IF(B2:B15<>"",B2:B15)),FALSE,FALSE)

which must be confirmed with control+shift+enter, not just with enter.

(b) With built-in functions:

=SUMPRODUCT(--(A2:A15="Include"),--(B2:B15<>""),--((MATCH(A2:A15&"#"&B2:B15,A2:A15&"#"&B2:B15,0)=ROW(INDEX(A2:A15,0,0))-ROW(A2)+1)))

Thanks so much for this string, it was exactly the info I needed...

But - I tried this with my data (4000 rows, one column with values "Include"
and "Exclude" that is the criteria for counting the unique text values in the
second column), and ended up with a formula result 1043.4907. Do I round up
or down???

=SUMPRODUCT((A2:A3964="Include)/COUNTIF(D23964, D23964&""))

I don't understand how the formula works or I would troubleshoot it myself

:

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Aladin Akyurek]

The option (b) can be shorter:

=SUMPRODUCT(--(A2:A15="Include"),--(B2:B15<>""),--((MATCH(A2:A15&"#"&B2:B15,A2:A15&"#"&B2:B15,0)=ROW(A2:A15)-ROW(A2)+1)))

I wonder if

=SUMPRODUCT(--(A2:A15="Include"),--(B2:B15<>""),--ISNUMBER(MATCH(MATCH(A2:A15&"#"&B2:B15,A2:A15&"#"&B2:B15,0),ROW(A2:A15)-ROW(A2)+1,0)))

would calculate faster.

Two options...

{a) If you have the latest version of Longre's morefunc.xll add-in:

=COUNTDIFF(IF(A2:A15="Include",IF(B2:B15<>"",B2:B15)),FALSE,FALSE)

which must be confirmed with control+shift+enter, not just with enter.

(b) With built-in functions:

=SUMPRODUCT(--(A2:A15="Include"),--(B2:B15<>""),--((MATCH(A2:A15&"#"&B2:B15,A2:A15&"#"&B2:B15,0)=ROW(INDEX(A2:A15,0,0))-ROW(A2)+1)))

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.