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.

"Rif" wrote:

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(D2:D43&"",D2:D 43&"",0)),MATCH(D2:D43&"",D2:D43&"",0))0)*1)

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

"Rif" wrote:

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.

"Biff" wrote:

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