2

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.

spreadsheet

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...

2 Answers 2

1

There are a bunch of options.

Probably the simplest is to change your formula to:

=IF(ISNA(MATCH(B2,monitoring!A:A,0)),"",1)

and then change the value settings for the column to Sum instead of Count.

If you must have the field still display Yes, then format the field:

 Value Field Settings --> Number Format --> Custom:  "Yes";;

For a more general solution, you can replace all the null strings/False/0 entries with true nulls, and then create the pivot Table.

Original Data

enter image description here

So as not to destroy your original data and formulas, you can do this in Power Query. All the steps, except for the "added column" can be done from the UI.

Steps

  • Get Data from Table/Range
  • Select all the columns
    • Replace (in separate steps) "", false and 0 with null (written out in small letters)
  • With all the columns selected, Unpivot
    • This will result in a two column table with column labels of Attribute and Value
  • Add a custom or conditional column whereby if the Attribute column = company, show the Value column, else null

enter image description here

  • Select the new Company column and Fill Down
    • This will fill down the populated cells to replace the nulls.
  • Pivot the Attribute column
  • delete the company column that has just the count of companies.
  • Close and Load to wherever.

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="autoServers"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"company", type text}, {"server", type text}, {"Vmname", type text}, {"monitored", type text}, {"backed up", type text}, {"app1 installed", type any}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"company", "server", "Vmname", "monitored", "backed up", "app1 installed"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",false,null,Replacer.ReplaceValue,{"company", "server", "Vmname", "monitored", "backed up", "app1 installed"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",0,null,Replacer.ReplaceValue,{"company", "server", "Vmname", "monitored", "backed up", "app1 installed"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value2", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Company", each if [Attribute] = "company" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Company"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Attribute]), "Attribute", "Value", List.Count),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"company"})
in
    #"Removed Columns"

Your original Pivot table is on top. Underneath is the PT generated after processing through PQ

Pivot Tables

enter image description here

If you make any changes in your data, Refresh All should refresh everything.

4
  • @Vitas Your edit seems to refer to a different question than your original. If the original question has been answered, you should mark the response as such and start a new question. Jan 16, 2020 at 13:22
  • the problem is still the same "Excel pivot table counts empty cells as having a value". If I made a new question it would have the same subject and the same example table... not sure I should do it like that?
    – Vitas
    Jan 17, 2020 at 13:16
  • @Vitas If you cannot alter your various column formulas to return a 1 or 0, I have posted a more general (and more complicated) solution which should work for you. Jan 17, 2020 at 20:03
  • thank you I will check it later and let you know!
    – Vitas
    Jan 21, 2020 at 18:15
0

I came across the same problem. My solution was to create a filter on column names in data sheet. Select to show only blank values in each column one by one. Then select those blank cells filtered out and press Delete on keyboard. Boom, Pivot Table counts as expected.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .