Count Unique Values in a column, with critera, exclude empty cells

[tyrdrannoy]

I have two columns of data such as this:
A B
R 12345
N 15769
R 12345
R 78910
<null> <null>
N 15769
<null> <null>

What I need is to be able to count unique values in col B, where col A = "R"
When I use the sumproduct method, I always get returned #NA.

 

[Eduardo]

Hi
try
=SUMPRODUCT((a1:a14="R")/COUNTIF(b1:b14,b1:b14&""))

change range to fit your needs, but remember you need the same range in both
parts of the formula

if this helps please click yes, thanks

 

[T. Valko]

Try this array formula** :

=SUM(--(FREQUENCY(IF((A2:A10="R")*(B2:B10<>""),B2:B10),B2:B10)>0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

Try that formula against this data:

R...1
N...1
R...1
N...2

 

[tyrdrannoy]

No - this is the formula that keeps returning #NA.

 

[tyrdrannoy]

Can't change the data - it comes from an external source.

 

[tyrdrannoy]

This one returns #N/A as well

Try this array formula** :

=SUM(--(FREQUENCY(IF((A2:A10="R")*(B2:B10<>""),B2:B10),B2:B10)>0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

I think you've misunderstood what this is supposed to mean.

=SUMPRODUCT((a1:a14="R")/COUNTIF(b1:b14,b1:b14&""))

Test that SUMPRODUCT formula against that sample data I posted and you'll
see that the SUMPRODUCT method has a potential flaw.

 

[T. Valko]

This one returns #N/A as well

The only thing that will cause that formula to return #N/A is if there is
already #N/A errors in either range or you use unequal range sizes.

 

[T. Valko]

EntryScreen!E1:E429
EntryScreen!I4:I429

You're using unequal range sizes. The range sizes must be the same.

 

[tyrdrannoy]

I see now! I didn't see the 1 in there. Sneaky little number!

Thanks!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

I see now! I didn't see the 1 in there. Sneaky little number!

Thanks!