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)


"carl" wrote in message
...
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.

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!
==============================================



"Bob Phillips" wrote in message
...
=SUMPRODUCT((A1:A20=1)/COUNTIF(B1:B20,B1:B20&""))

--

HTH

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


"carl" wrote in message
...
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 wrote:
=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.

"Aladin Akyurek" wrote in message
...
Bob Phillips wrote:
=SUMPRODUCT((A1:A20=1)/COUNTIF(B1:B20,B1:B20&""))


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.

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

"Bob Phillips" wrote:

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

--

HTH

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


"carl" wrote in message
...
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.

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:B1 5)),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)))

K wrote:
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

"Bob Phillips" wrote:


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

--

HTH

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


"carl" wrote in message
...

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.





--

[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 Akyurek wrote:
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:B1 5)),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)))


K wrote:

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
"Bob Phillips" wrote:


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

--

HTH

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


"carl" wrote in message
...

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.






--

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

Try...

=SUM(IF(FREQUENCY(IF((A1:A6=1)*(B1:B6<""),MATCH(B 1: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!

In article ,
"carl" wrote:

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.

carl wrote:
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.