none
Countifs under Calculate field in pivot RRS feed

  • Question

  • Hi All

    I am preparing a dashboard for server compliance which will contain Server name in 70 countries & compliance against monitoring, patch, Antivirus in a sheet. While doing pivot we can't add multiple values which says total server count VS Monitoring compliant VS Patch compliant.

    So i taught to add calclate field under formulas i tried to point the table / sheet contains raw data & used countifs field but its not working but the same countifs working under normal sheet.

    Sample is =COUNTIFS(Table_[City],B4,Table[servertype],"Virtual") but it doesnt work under calculate field, could someone suggest


    Ganapathy

    Tuesday, January 5, 2016 1:01 AM

All replies

  • Hi, ganapathys_s

    According to your description, I have reproduced this issue, I suggest you could create Macro to add calclate field under formulas, refer to below code:

    val1 = Application.WorksheetFunction.CountIfs(ActiveSheet.Range("A2:B7"), "=Jack")
    ActiveSheet.PivotTables("PivotTable2").CalculatedFields.Add "Test", "=" & val1
               xlDataField 


    Otherwise this is the forum to discuss questions and feedback for Excel for Developers, if you want to add calclate field under formulas manually, I suggest that you could post your issue to the Technet forum for Excel

    https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.


    Thursday, January 7, 2016 1:12 AM
  • As it's a dash board you are doing I probably wouldn't try to do a summary level thing in the pivot.

    You can use the same data source but just use normal excel to calculate the various states and maybe display as a graph- the pivot table could then be used to expand by country, site etc if the users want to drill down.

    Also if your data source has a different field for each of the things you are looking to measure you could probably then display the way you want- this could be done using either a pivot table in sql server or create 3 new columns in your source data and us if statement to populate or lastly create a pivot showing the criteria in the "Columns" section of the pivot table- then do another pivot using this pivot as it's source.

    HTH

    D

    Thursday, January 7, 2016 9:40 AM