CODEWITHSUNDEEP
excelpivot
Bigjo
Bigjo

Reputation: 633

excel pivot table count blank names or filled name

I have a table with three columns (division, functions, employee). There are 5 different divisions. The functions are unique and always filled in. The employee is unique but can be blank if nobody is on that function.

Now I want to make a pivot table where I see for each division the count of empty seats and the count of filled seats. So I have the 5 divisions with each time two values. For example: HR has 45 times a blank employee and 91 times an employee.

Upvotes: 0

Views: 356

Answers (1)

Raugmor
Raugmor

Reputation: 566

You can try the following:
add an extra column to the original data set with a formula: =IF(A2="","Blank","OK"), where column "A" is the column with employee names which can be blank, and fill it down. Then do a pivot table on this table, place "Division" to column labels, extra column to column labels, and "count of function" to the values. This way it will show you count of "Blanks", "OK" and also the grand total.

Upvotes: 1

Related Questions