Pivot table query
 

I have the following data,

Name Amount
David 400
Homer 100
David 300
Homer 100
Sanjiv 600


How can I use a pivot table to aggregate the total the amount for each
person, but only show if the amount is greater than 500?

E.g.

Name Amount
David 700
Sanjiv 600

Pivot table query
 

Try this:

With
A1:B6 containing your posted list
Name Amount
David 400
Homer 100
David 300
Homer 100
Sanjiv 600

Then
C1: SHOW
C2: =SUMIF($A$2:$A$6,A2,$B$2:$B$6)500
Copy C2 down through C6

Now A1:C6 contains this list
Name Amount SHOW
David 400 TRUE
Homer 100 FALSE
David 300 TRUE
Homer 100 FALSE
Sanjiv 600 TRUE

When you create the Pivot Table, use these settings
ROW: Name, SHOW
Date: Sum of Amount

Then....On the Pivot Table
Click on the SHOW heading and leave only TRUE checked.

The final view of the Pivot Table will be

Sum of Amount
Name SHOW Total
David TRUE 700
Sanjiv TRUE 600
Grand Total 1300

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"nc" wrote:

I have the following data,

Name Amount
David 400
Homer 100
David 300
Homer 100
Sanjiv 600


How can I use a pivot table to aggregate the total the amount for each
person, but only show if the amount is greater than 500?

E.g.

Name Amount
David 700
Sanjiv 600

Pivot table query
 

Thanks a lot for your help.

Is there of doing it all within the pivot table without the need of creating
the extra column, maybe using calculated fields or calculated items.


"Ron Coderre" wrote:

Try this:

With
A1:B6 containing your posted list
Name Amount
David 400
Homer 100
David 300
Homer 100
Sanjiv 600

Then
C1: SHOW
C2: =SUMIF($A$2:$A$6,A2,$B$2:$B$6)500
Copy C2 down through C6

Now A1:C6 contains this list
Name Amount SHOW
David 400 TRUE
Homer 100 FALSE
David 300 TRUE
Homer 100 FALSE
Sanjiv 600 TRUE

When you create the Pivot Table, use these settings
ROW: Name, SHOW
Date: Sum of Amount

Then....On the Pivot Table
Click on the SHOW heading and leave only TRUE checked.

The final view of the Pivot Table will be

Sum of Amount
Name SHOW Total
David TRUE 700
Sanjiv TRUE 600
Grand Total 1300

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"nc" wrote:

I have the following data,

Name Amount
David 400
Homer 100
David 300
Homer 100
Sanjiv 600


How can I use a pivot table to aggregate the total the amount for each
person, but only show if the amount is greater than 500?

E.g.

Name Amount
David 700
Sanjiv 600