I am baffled by this weird behavior of pivot tables. Imagine an example: at my work we have companies and servers. I make Excel spreadsheets with various information like what is backed up and what is monitored. In this simple example I want a pivot table which will tell me how many servers are backed up and monitored for each company. It would work if I typed all the values in the table manually, but naturally I use formulas to populate the fields with either "yes" or empty string "".
For some reason the Count of area of pivot table always counts all rows regardless of if they have a value or empty string. I don't know how to make this work.
Notice that if you delete formulas and do right-click / refresh on the pivot table it will then calculate properly
Here is the file so you can see the formulas and try to fix it (updated 15.1.)
https://drive.google.com/file/d/17QIGEkvXbMKRmCrS8AoHy7rti8k6X1xJ/view?usp=sharing
EDIT: Thanks to Ron I have a workaround for the D and E col (replaced "yes" with 1 as he suggests). Unfortunately this solution is not applicable for many other situations. I have added cols C and F as examples which I would also love to be represented in Pivot table, but really have no idea how to persuade excel to understand it :-] Imagine that some servers are physical, thus the C will be empty for that line because hostname was not found in the VMWare extract. I would like to know number of virtual machines for that company. The status of app1 is generated by some powershell script which scans computers in AD, pings them and returns TRUE or FALSE in case server responds. 0 means server is unreachable. I would like to count how many times there is TRUE.
I am thinking of conditional formatting - there it is possible to write a formula to determine cell format. I don't see such possibility in Pivot table unfortunately...