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]

[TABLE="width: 593"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Title[/TD]
[TD]No. Applications[/TD]
[/TR]
[TR]
[TD]Marketing Administrator (Shows and Events) - Full time
Marketing Administrator (Shows and Events) - Full time
[/TD]
[TD]
19
9

[/TD]
[/TR]
[TR]
[TD]Retail Operations Support Help Desk Administrator - Full time, 37.5 hours per week[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Customer Service Assistant (Tills) - Part time, 13.5 hours per week (weekends)[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Customer Service Assistant - Part time 5.5 hours per week (Sunday)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Feedstore Assistant - Part time, 16 hours per week[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Administrator - Full time[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Stock Team Member (Goods inwards) - Full time DUPLICATE COPY[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Business Centre Manager - Full time[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Trainee Product Manager/Product Manager[/TD]
[TD]15
[/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ROW LABELS (Job Title)<strike></strike>
[/TD]
[TD]COUNT of number of applications<strike></strike>
[/TD]
[TD]SUM of number of applications<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Production / Industrial Cleaner<strike></strike>
[/TD]
[TD]1<strike></strike>
[/TD]
[TD]0<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Production Operative - Full Time<strike></strike>
[/TD]
[TD]5<strike></strike>
[/TD]
[TD]0<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]


This is an example of the data in the pivot table

 

[steve the fish]

So you would expect to see?

[TABLE="width: 624"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Count of No. Applications[/TD]
[TD]Sum of No. Applications[/TD]
[/TR]
[TR]
[TD]Administrator - Full time[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Business Centre Manager - Full time[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Customer Service Assistant - Part time 5.5 hours per week (Sunday)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Customer Service Assistant (Tills) - Part time, 13.5 hours per week (weekends)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]Feedstore Assistant - Part time, 16 hours per week[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Marketing Administrator (Shows and Events) - Full time[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Retail Operations Support Help Desk Administrator - Full time, 37.5 hours per week[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Stock Team Member (Goods inwards) - Full time DUPLICATE COPY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Trainee Product Manager/Product Manager[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]88[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 613"]
<tbody>[TR]
[TD="width: 138, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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