PivotTable that meets conditions.

  • Thread starter Thread starter lukus2005
  • Start date Start date
L

lukus2005

I finally was able to create a PivotTable.

Now I'd like to be able to generate a PivotTable that shows only the
data that meets specific conditions.

Conditions such as...

- List only contracts that lost money. (Gross Profit column equals 0
or less)
- List only contracts that are completed. (where an X can be found in
the Done column)

Also, would like to add a field in my totals that shows the overall
profit percentage for all completed contracts. This is tricky as it
must divide the Total Gross Profit by the Total Contract Value but
only for contracts are have an X in the Done column. In my
spreadsheet, I can easily do this using the following formula...

=SUM(L15:L32)/SUMIF(C15:C32,"X",D15:D32)

Column L being each job's Gross Profit.
Column C being a flag to indicate if job is completed.
Column D being the Contract Value of each job.

Now how do I incorporate such a calculated field in my PivotTable. I'd
like to show a sub-total for all completed contracts with that client
along with the average profit percentage based the above formula.
 
Hi,

1. Open the Filter on the Done column and remove all checks except the one
beside X.

The second question is version dependent - are you using 2007 or an earlier
version. It is also depending on wheather the Gross Profit colum is a Data
field or a Row field. Let us know

If this helps please click the Yes button

Cheers,
Shane Devenshire
 
I'm using version 2002 of Excel.

The Gross Profit column is a Data field on the layout.

As for the Filter you are referring to, I'm not sure what you mean.
The Done column only shows an X when the job is billed 100% therefore,
it is either X or empty.
 
Back
Top