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.

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

--

Regards,
Nigel
(E-Mail Removed)



"sraynes" <(E-Mail Removed)> wrote in message
news:7658F9F6-C838-43CE-81F3-(E-Mail Removed)...
>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 - 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
> (E-Mail Removed)
>
>
>
> "sraynes" <(E-Mail Removed)> wrote in message
> news:7658F9F6-C838-43CE-81F3-(E-Mail Removed)...
> >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.
> >
>