Unique Entries with Conditions

[Rif]

Say I have items in column A that are not unique. Also, say that if
something is filled in on another column (B), to count that unique item in
column A.

Column A is sorted. Column B may or may not be.

Graphically:

(A)
040000-000001
040000-000002
040000-000003
040000-000003
040000-000003
040000-000004
....

(B)
COMPLETE
ABORT
INVALID
INVALID
COMPLETE
COMPLETE
....


... should return 4. However, if one (or two) of the instances for
040000-000003 are null (blank), it *still* should return 4 (as one of the
instances have a "mark"). In other words, the only time I should see a
decrease in number of uniques in column A is if and only if every occurence
of that unique in column A has a null for column B.

Any ideas?

 

[Biff]

Hi!

Not sure I understand your last paragraph but try this and see if it does
what you want:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=SUM(--(FREQUENCY(IF(B1:B6<>"",MATCH(A1:A6&"",A1:A6&"",0)),MATCH(A1:A6&"",A1:A6&"",0))>0))

Biff

 

[Rif]

Hmm... as an array formula, it gives me a zero. I'm pretty sure I've entered
it correctly, making the necessary changes.

I should be more clear. I want all "uniques" from column A but only if at
least one of the instances have a word (such as COMPLETE or ABORT) in column
B.

So in my example, if 040000-000003, which has 3 instances, has 1, 2 or 3
marks in column B, then I want that "unique" to be counted. If *NO* marks
are present for this unique in column A, I do not want it counted.

 

[Rif]

Weird.

When I enter a second condition outside of your FREQUENCY condition, it works.

So instead of:

=SUM(--(FREQUENCY(IF(B1:B6<>"",MATCH(A1:A6&"",A1:A6&"",0)),MATCH(A1:A6&"",A1:A6&"",0))>0))

and converting it to

=SUM((FREQUENCY(IF(Y2:Y43<>"",MATCH(D243&"",D243&"",0)),MATCH(D243&"",D243&"",0))>0)*1)

it gives me what I need. Does this mean I should use the other notation for
arrays? I'm confused.

 

[Rif]

I should have said a resounding thank you!

Still, I don't understand why I have to add the "*1" as a second condition
to make it work. Ugh.

 

[Biff]

Either formula will work.

In your formula multiplying by 1 is the same as the double unary "--" in my
formula. They're both converting booleans into numbers so that SUM can do
its thing.

Just to FYI:

If there will be no empty cells within the range D243 then you can remove
all instances of &"" from the formula. That just makes it robust against
empty cells in that one range.

Biff