Excel PivotTable calculation

[jhaganjr]

Simple data setup ... each record is a sales lead. Each record includes a result column - sold or unsold. Simple example might look like ...

Lead Result
6/1/10 Open
6/2/10 Sold
6/3/10 Sold
6/4/10 Unsold

I would like a "report" result like this ...

CountOfLead CountofResult=Sold Rate
4 2 50%

I have a working solution for it. But, using a PivotTable does anyone know how to ...

a) Filter only the Result field to pull "Sold" values, while all the Leads are counted? If I apply a normal filter to the table for only Result=Sold, CountOfLead = 2 and CountOfResult = 2. I want 4 and 2 respectively.

b) Get that rate calculation to work automatically in a PivotTable?

I don't think this is possible in a PivotTable, but thought I'd ask.

Thanks.

 

[SkipVought]

Hi,

More than likely, it is possible to calculate this from your source data.

Please post an example of your source data, supporting the PT results you previously posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jhaganjr]

Sorry ... don't know how to attach a file. The 8 cell example is really exactly the source data to work with.

Goal stated in words ... which I can do outside a PivotTable ... but trying to take advantage of PT flexibility ...

Count all the lead records
Count only the sold results
Show me the percent of sold/all

Thanks.

 

[SkipVought]

Using Named Ranges...
[tt]
Count of Lead: =COUNTA(Lead)
CountofResult=Sold: =COUNTIF(Result,"Sold")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jhaganjr]

Skip,

I use those functions now. How does one use those in a PivotTable? As far as I can tell PT's do not provide that kind of formula insertion ... so that I can utilize the PT flexibility, while also getting the analysis from the functions you suggested. Is it possible?

Thanks, again.

 

[SkipVought]

Don't know who to get it IN the PT but...
[tt]
Row Labels Count of Lead Pct
Open [red]1[/red] =[red]B2[/red]/COUNTA(Result)
Sold 2
Unsold 1
Grand Total 4
[/tt]
if that is your PT then a formula in the adjacent column returns the value of interest


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jhaganjr]

You're dead on again, Skip. But formulas outside the PT are rendered useless as soon as filter options change the layout of the PT results.

I just don't think I can get the best of all worlds ... the flexibility of the PT PLUS the power of complex functions.

I appreciate your help.