Pivot table - count a text field

Question

As the standard disclaimer, I am relatively new to pivot tables in Excel/Office365. Hopefully this is an easy solve for those with Excel skills.

I have a pivot table that shows open vulnerabilities for servers.  As you can see below, the city has a number of servers grouped underneath and then the vulnerability name and finally the installation status.  I would like to use a calculated field to count the number of servers (device total) as shown in the 2nd pivot table.  For my example, I manually counted the servers (green highlight) to show what I am trying to achieve via automation. I tried using =COUNT(location) however the result is always 1 regardless of the number of servers present.

So what formula / method should I use to count how many servers are under each city location ?  Thanks.

Answer 1

Thanks Ashish!

Couple issues - first off I had to figure out why PowerPivot would not open. Traced it to an McAfee ENS exploit prevention policy.  So that was easy enough to solve however the 2nd issue is mine. I now have to learn to use PowerPivot!  I am looking at your example and trying to reverse engineer how you did it 8-)

One question - is the PowerPivot method the only way the device count can be generated? The open vul reports need to be generated weekly and I need to find a method that is the most time efficient and it seems the PowerPivot might be a tad intensive for a weekly report. Most likely its task better suited to SQL Reporting Services.

Answer 2

Hi,

You may download my solution workbook from here.  I have used the PowerPivot to solve the question.

Hope this helps.

Answer 3

Can you upload a sample file to Onedrive without confidential / sensitive data and share the link here to work out the answer?

Here is the example XLSX file:

https://1drv.ms/x/s!AtlbfJ0wwHl5qjTZ50TBY\_aORQhI

Answer 4

Can you upload a sample file to Onedrive without confidential / sensitive data and share the link here to work out the answer?