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

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

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

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

tyrdrannoy

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

T. Valko

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

T. Valko

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

tyrdrannoy

=SUM(--(FREQUENCY(IF((EntryScreen!E1:E429="R")*(EntryScreen!I4:I429<>""),EntryScreen!I4:I429),EntryScreen!I4:I429)>0))

there are no #N/A values in the data - the data comes directly from a data
source, and has no formulas in it.

currently, data after line 130 is blank, but later, could have data in it
when the data gets refreshed.
 
T

T. Valko

EntryScreen!E1:E429
EntryScreen!I4:I429

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Number of Unique Cells That Don't Have Certain Characters 0
Average Unique 7
Counting unique values in multiple columns 4
Formula for counting cells with value 'x' provided Col U = 'y' 5
Count Unique Combinations 6
Count Blank Cells with Conditions 3
How do I count # of cells with same value other column? 4
Isolate UNIQUE combination of Z cells from a range N that adds up 3

Top