Count unique records that also meet criteria

[Brigette]

I'm not sure if there is a formula to calculate what I want or if it will
have to be a macro. I'd prefer a formula if possible. I have a workbook
that has 2 sheets "Charts" and "Tooling PLOB". On my "Charts" tab I need it
to count unique records from the "Tooling PLOB" that also meet certain
criteria so I can chart it. (My boss doesn't like pivot tables.)

For Example, from the "Tooling PLOB" I want to count how many in column I
are "In Work" and is also a unique record. Column K has job #s that may be
listed several times. I want each job # to only be counted once. Thanks for
any help.

-Brigette

 

[T. Valko]

My boss doesn't like pivot tables.

Your boss is smart.

I want each job # to only be counted once.

Are these numbers like regular numbers? Or, are they alpha-numeric strings?
Any leading 0s?

 

[Brigette]

Alpha numeric: ABC1234. No leading 0s.

 

[T. Valko]

Assumes no empty cells in job number range (otherwise, you'll get an error).

Array entered** :

=SUM(IF(FREQUENCY(IF(I2:I20="In
Work",MATCH(K2:K20,K2:K20,0)),ROW(K2:K20)-ROW(K2)+1),1))

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