Pivot Table with Calculated Field

[animas]

<title>Excel Jeanie HTML</title>Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>Master</td> <td>Value</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">100888</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">100888</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">100999</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">100999</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">100222</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">100888</td> <td style="text-align: right;">1</td></tr></tbody></table>

I need to enter a calculated field with following condition:
=If(Count(Master)=Sum(Value),Master,"")

Here say Master=100888, so Count(Master)=3, Sum(Value)=2(for master 100888). So calculated field should show "". For master 100999 calc field will show "100999" since count=sum.

How to do this?

 

[Andrew Poulsom]

In C1 enter Calc. In C2 enter:

=IF(COUNTIF(A:A,A2)=SUMIF(A:A,A2,B:B),A2,"")

and copy down. Add that column to your pivot table.

 

[animas]

i did that earlier. i have 250000 data. this column will slow down the excel. i am using pivot table to filter the MASTER first.

so i can't do this using calculated field?

 

[Andrew Poulsom]

Sorry, no.