question working with pivot table

[Guest]

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

 

[Earl Kiosterud]

Russ,

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

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

 

[Guest]

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

 

[Earl Kiosterud]

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

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

 

[Guest]

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