Count unique items in pivot table results

[BW.Wooster]

Hi all,

I have been looking for a way extracting some data from an excel
spreadsheet and have been told pivot tables are the way to do this.
However the 'count' option which appears at the end of each group is
adding up the totals rather than counting the number of instances. For
Example, this is my table:

------
Count of contact type

Status Last Name Total

First Interview Smith 6
Jones 2
Withers 3

First Interview Count 11

Second Interview Smith 2
Jones 2
Withers 1

Second Interview Count 5

------

What I would like is for the 'Count' column to show the number of
instances rather than a total, like this:

------
Count of contact type

Status Last Name Total

First Interview Smith 6
Jones 2
Withers 3

First Interview Count ?? 3

Second Interview Smith 2
Jones 2
Withers 1

Second Interview Count ?? 3

------

I have tried a variety of options, including adding following these
instructions:

http://groups.google.co.uk/group/mi...b45c4?lnk=st&q=pivot+table+count&rnum=3&hl=en

But that does not seem to provide the answer.

Any help would be greatly appreciated as I have to go through at the
moment counting the rows manually!

 

[bj]

Using the thread method, You need to insure that it counts first, sercond,
etc interviews separately
if the data is sorted so that the first interviews are all above the second
and so forth
change the B$2 reference at the top of each group to the top row reference.
if they are not grouped, the equation needs to be changed to something like
a sumproduct with appropriate references to do the job.
How are they groups and what lets you know whether it is first second or
third interview?

 

[BW.Wooster]

Thank you for your reply,

Within the original data there is a column with the 'status' data
(such as 1st, 2nd, 3rd interview etc) and another column with the last
name data in. The original data is exported from a database and is not
grouped in any way. The difficulty is that people in my company attach
the status of 1st, 2nd, 3rd, etc interview to the people in the
database more than once for each job (the data for different jobs is
exported into separate sheets, each with their own pivot table). I
only want to count each type of status once for each individual.

Below is an example of the original data from which the pivot table is
prepared:


Name Status Job
Smith First Interview Head of Informatics
Jones Second Interview Head of Informatics
Withers First Interview Head of Informatics
Jones First Interview Head of Informatics
Withers Second Interview Head of Informatics
Smith First Interview Head of Informatics
Smith First Interview Head of Informatics
Withers Second Interview Head of Informatics
Jones First Interview Head of Informatics
Jones Second Interview Head of Informatics
Smith First Interview Head of Informatics
Withers Second Interview Head of Informatics

I have tried your suggestion of grouping the data and using the
formula on each type of 'status' but that does not seem to give me the
same answers as doing a manual count of the original table.

Does this answer your question? and thank you for your help,

 

[Dave Peterson]

Debra Dalgleish shows a way:
http://contextures.com/xlPivot07.html#Unique

 

[bj]

=1/SUMPRODUCT(--($A$2:$A$13=A2),--($B$2:$B$13=B2))
should total to 1 for the same name and same status
adding a field with this equation should give you the number
Are you really interested as to how many first interviews each candidate had?

 

[BW.Wooster]

That seems to have done the trick! Many thanks to both of you for your
help.

'Interested' is not the word I would use! This is for reporting
purposes to show progress against individual roles we are recruiting
for, and my boss wants to know.

Thanks again,