Counting Unique Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
=SUMPRODUCT((A1:A20=1)/COUNTIF(B1:B20,B1:B20&""))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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!
==============================================
 
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!
 
carl said:
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.
Click to expand...

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.
 
Bob said:
=SUMPRODUCT((A1:A20=1)/COUNTIF(B1:B20,B1:B20&""))
Click to expand...

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.
 
Aladin Akyurek said:
Did you test that? Here is a sample:
Click to expand...

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.
 
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(D2:D3964, D2:D3964&""))

I don't understand how the formula works or I would troubleshoot it myself
 
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(D2:D3964, D2:D3964&""))

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

:
Click to expand...

--

[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.
 
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.

Aladin said:
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)))
Click to expand...

--

[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.
 
Back
Top