question working with pivot table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xrbbaker - 20 Apr 2007 14:12 GMT

I have raw data like this showing what % of their time was involved on a
project.

        JAN    FEB    MAR
A    Tom    .25        .5
A    Sam    .25    .4    .5
A    Phil    .25    .5    .5
A    Pete    .5    .5    .75
B    Sam    .1    .4    .5
B    Tom            .4

I want to show the total of how many unique people were involved across all
projects each month. If they were involved anything > 0, then that counts.
Thus the desired output would be:

    JAN    FEB    MAR
TOTAL    4    3    4

I put a formula in the raw data so that it is either a 1 or 0. I can make a
pivot table using the raw data. The PT groups the people, but then sums the
raw data, (as it should) ending up with double counting for my purposes.

        JAN    FEB    MAR
Tom        1        2
Sam        2    2    2
Phil        1    1    1
Pete        1    1    1
TOTAL    5 4 6

How can I set things up so that I only count a person once, even if they
worked on 2 different projects during the same month?

Thanks - Russ

Reply to this Message

Earl Kiosterud - 20 Apr 2007 22:15 GMT

Russ,

Use the count function in the second (person) column. DOn't have it a subordinate of the
first column.

Signature

Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already
top-posted, the thread gets messy. When in Rome... :)

-----------------------------------------------------------------------

>I have raw data like this showing what % of their time was involved on a
> project.
[quoted text clipped - 29 lines]
>
> Thanks - Russ

Reply to this Message

xrbbaker - 23 Apr 2007 13:30 GMT

Eric,

Thanks, but I don't see how that will work. I need to show unique people by
month. If I count (non-blank) people cells or count (non-blank) month cells,
I will over count people because one person may work (partially) on 3
different projects in a given month. Am I not understanding your solution?

thanks

> Russ,
>
[quoted text clipped - 34 lines]
> >
> > Thanks - Russ

Reply to this Message

Earl Kiosterud - 23 Apr 2007 23:43 GMT

Eric,

I didn't read your question correctly. I can't determine how you got the 4, 3, 4 results
you want in the example. It appears to me that Tom, Sam, Phil and Pete have entries in the
Feb column, so the result should be 4, not 3.

Take a look at Chip Pearson's page http://www.cpearson.com/excel/duplicat.htm. There is
stuff for duplicates, including counting uniques. Maybe you can get where you need to be
starting there.

Signature

Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already
top-posted, the thread gets messy. When in Rome.

-----------------------------------------------------------------------

> Eric,
>
[quoted text clipped - 43 lines]
>> >
>> > Thanks - Russ

Reply to this Message

xrbbaker - 24 Apr 2007 00:36 GMT

Thanks Earl. I'll check it out.

Yeah trying to represent data is a mess here. Here where I want to get:

There were 4 unique people billing in Jan - Tom, Sam, Phil, Pete
There were 3 unique people billing in Feb - Sam, Phil, Pete
There were 4 unique people billing in Mar - Tom, Sam, Phil, Pete

Thanks

        JAN    FEB    MAR
A    Tom    .25        .5
A    Sam    .25    .4    .5
A    Phil    .25    .5    .5
A    Pete    .5    .5    .75
B    Sam    .1    .4    .5
B    Tom            .4

> Eric,
>
[quoted text clipped - 52 lines]
> >> >
> >> > Thanks - Russ

Reply to this Message

Rate this thread: