Count unique values - Match and conditional IF

[sraynes]

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.

 

[Nigel]

Sounds like you need to apply a Pivot table to your data. Look up this
approach in Excel help for more details

 

[sraynes]

Nigel - I hadn't thought of this simple solution. I was "stuck" on wanting a
total by division in a cell of my status page for the project. I'll do this
and see if it's enough. Maybe I was overthinking it.

thanks
-----------