multiple count Pivot table in Excel

Question

I'm trying to put togeter a pivot table from an Excel spreadsheet. The spreadsheets look similar to the following:

DeptHead, Emp,  Increment
x,        A,      2.5%
x,        B,
y,        C,      1.5%
y,        D,
y,        E,      2.0%

I would like to make a pivot table that looks like the following;

DeptHead, CountOfEmp,  CountOfIncrement
x,        2,           1 
y,        3,           2

So it provides a count of total number of Emps and total number Increments for each DeptHead ignoring the blanks. I have tried to do this in many ways in Pivot table, but the two counts are only appearing in rows and not in columns as above.

Is there any way to achieve this please? Thanks

Answer 1

Does your layout look like this? And, you might select the 'classic' layout as well, in the 'Display' tab on the 'Pivot Table Options' dialog form.

Answer 2

It could be you are just not placing the fields correctly in the Pivot Table field list.

Place the Emp and Increment fields in the "Values" section.