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 (I couldn't get the copy and paste formatting to work):


╔═════════════╦═══════════╦═════════╗
║ 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.