lundi 27 avril 2015

Pivot Table - Calculated Field - Counting Distinct Values For with If Condition Met - Excel 2013

How can I create a calculated field in a pivot table that will count distinct values of Field1, given that Field2 meets a particular condition?

My limitations:

  • Needs to be in a pivot table
  • Needs to be a calculated field
  • The calculated field needs to be a unique count "formula", not simply a field setting change

Here is an example of what I am trying to achieve.

Raw data:

╔═════════════╦═══════════╦═════════╗
║ Date Period ║ Client ID ║ Field 2 ║
╠═════════════╬═══════════╬═════════╣
║           1 ║ A         ║       1 ║
║           1 ║ A         ║       1 ║
║           1 ║ A         ║       1 ║
║           1 ║ B         ║       1 ║
║           1 ║ B         ║       1 ║
║           1 ║ C         ║       1 ║
║           2 ║ A         ║       1 ║
║           2 ║ A         ║       1 ║
║           2 ║ B         ║       0 ║
║           2 ║ C         ║       0 ║
║           2 ║ C         ║       0 ║
╚═════════════╩═══════════╩═════════╝

Here is what the Pivot Table output would look like:

╔═════════════╦═══════════════════════════════════╦═══════════════════════════════════╗
║ Date Period ║ Distinct Clients where Field 2= 1 ║ Distinct Clients where Field 2= 0 ║
╠═════════════╬═══════════════════════════════════╬═══════════════════════════════════╣
║           1 ║                                 3 ║                                 0 ║
║           2 ║                                 1 ║                                 2 ║
╚═════════════╩═══════════════════════════════════╩═══════════════════════════════════╝

I have seen methods of using array functions (a combo of SUM, IF, FREQUENCY, and MATCH) for non-pivot table data. Can I do this with Pivot table fields?

I don't have any VBA background.

Aucun commentaire:

Enregistrer un commentaire