Pivot Table - values showing as SUM in ROW but not in VALUES field

[SuziBaz]

I have created a Pivot table created from a table of data for which I require the follow:

Number of Job Applications per vacancy.

I would like the pivot to show Job Title in the ROW and bring back the number of applications as a SUM in the VALUE field.
When I do this, the SUM comes back as ZEROs. I have checked my data for Text, Gaps and anything else untoward.

The wierd thing is.....If I put the Number of Applications into the ROW, it brings back the SUM correctly. As soon as I move the Number of Applications across to VALUE, it reverts to Zeros

Any ideas?

 

[steve the fish]

You just want a count in the values field dont you?

 

[SuziBaz]

COUNT brings back the number of occurences the Job Title appears

 

[SuziBaz]

For example, there is a job vacancy for a Yard Team Member which has been advertised 5 times throughout the year.
I would like to see the number of job applications for each of these vacancies.

The data set has this information already in a column

 

[steve the fish]

You need to give an example of the data in the columns. How is the pivot to seperate the adverts for the same job title?

 

[SuziBaz]

Title	No. Applications
Marketing Administrator (Shows and Events) - Full time Marketing Administrator (Shows and Events) - Full time	19 9
Retail Operations Support Help Desk Administrator - Full time, 37.5 hours per week	7
Customer Service Assistant (Tills) - Part time, 13.5 hours per week (weekends)	17
Customer Service Assistant - Part time 5.5 hours per week (Sunday)	1
Feedstore Assistant - Part time, 16 hours per week	4
Administrator - Full time	14
Stock Team Member (Goods inwards) - Full time DUPLICATE COPY	1
Business Centre Manager - Full time	1
Trainee Product Manager/Product Manager	15

<colgroup><col><col></colgroup><tbody>
</tbody>

So, for example I would like to see Marketing Administrator (Shows and Events) - Full time count 0f 2 vacancies and a sum of 28 applications

 

[SuziBaz]

ROW LABELS (Job Title)<strike></strike>	COUNT of number of applications<strike></strike>	SUM of number of applications<strike></strike>
Production / Industrial Cleaner<strike></strike>	1<strike></strike>	0<strike></strike>
Production Operative - Full Time<strike></strike>	5<strike></strike>	0<strike></strike>

<tbody>
</tbody>

This is an example of the data in the pivot table

 

[steve the fish]

So you would expect to see?

Row Labels	Count of No. Applications	Sum of No. Applications
Administrator - Full time	1	14
Business Centre Manager - Full time	1	1
Customer Service Assistant - Part time 5.5 hours per week (Sunday)	1	1
Customer Service Assistant (Tills) - Part time, 13.5 hours per week (weekends)	1	17
Feedstore Assistant - Part time, 16 hours per week	1	4
Marketing Administrator (Shows and Events) - Full time	2	28
Retail Operations Support Help Desk Administrator - Full time, 37.5 hours per week	1	7
Stock Team Member (Goods inwards) - Full time DUPLICATE COPY	1	1
Trainee Product Manager/Product Manager	1	15
Grand Total	10	88

<tbody>
</tbody>

<tbody>
</tbody>

 

[steve the fish]

I have a feeling your numbers may be text. Go to the column where the number of applications is housed. Highlight the column and press Data-Text to columns and then finish. Refresh the pivot.

 

[SuziBaz]

Awesome, thank you Steve the fish, this seems to have resolved the issue.
I had checked that the data in the column was pure numbers, no text, no missing data etc....but converting text to columns has resolved