Count unique values - Match and conditional IF
 

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(ServerListByO S!$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.

Count unique values - Match and conditional IF
 

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

--

Regards,
Nigel




"sraynes" wrote in message
...
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(ServerListByO S!$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.

Count unique values - Match and conditional IF
 

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

"Nigel" wrote:

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

--

Regards,
Nigel




"sraynes" wrote in message
...
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(ServerListByO S!$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.