I have the following scenario.  used to calculate the progress of a server
project.

Column C is the Division owning the hardware, several unique values repeated
as appropriate.  i.e. ABC, or XYZ
Column F is the serial number of the server. May or may not be unique. Some
hardware runs one server, others run mulitple servers on that serial number.
i.e. XYZ123 or 876HVS76
Column K is a Y if task is done, blank if not.

I've create this formula to count the unique number of items in F, so I know
how many pieces of hardware with that are the same serial number I have in
the inventory.

=SUM(IF(FREQUENCY(MATCH(ServerListByOS!$F$3:$F$162,ServerListByOS!$F$3:$F$162,0),MATCH(ServerListByOS!$F$3:$F$162,ServerListByOS!$F$3:$F$162,0))>0,1))

This works fine, but doesn't give me counts of unique serial numbers by
Division, which I need.  How do I get that?

Additionally, I want to know when the work is completed, meaning all items
for that serial number are marked with a Y in column K.  Then I want a total
for these by division.

In the end, I hop to report on total unique serial numbers by division, then
also the total of those unique serial numbers, that all have a Y in column K.

Division should always match on each row for that serial number.  Assume it
will.

Any help is greatly appreciated.


Column C varies, but for every match of Column F, C should match.  As the
project progresses, the K column should eventually all have Y's indicating
this piece of hardware is completed, and I want to sum a single item for
Division Column C.