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

  • Thread starter Thread starter tyrdrannoy
  • Start date Start date
T

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.
 
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
 
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.
 
This one returns #N/A as well

T. Valko said:
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.
Click to expand...
 
I think you've misunderstood what this is supposed to mean.
=SUMPRODUCT((a1:a14="R")/COUNTIF(b1:b14,b1:b14&""))
Click to expand...

Test that SUMPRODUCT formula against that sample data I posted and you'll
see that the SUMPRODUCT method has a potential flaw.
 
This one returns #N/A as well
Click to expand...

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.
 
EntryScreen!E1:E429
EntryScreen!I4:I429

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