Hi -

How do I make a formula in a pivot table based on the value of a column?
I have a pivot table with the results of totals by employee number of 4
types of loans, type 1, type 2, type 3 and type 4. I get the count and
amount of the loans by employee. I want to add type 1 to type 3 for each
employee and get a total count and total amount. I am stuck.

Example Data

Employee
Number Type Amount
457 Type 1 $578,459.00
922 Type 1 $4,578,884.00
922 Type 3 $588,900.50
311 Type 4 $657,844.00
457 Type 2 $9,587.00
457 Type 3 $654,789.00
311 Type 1 $159,753.00
922 Type 2 $258,456.00
311 Type 3 $951,753.00
457 Type 4 $987,357.00
311 Type 1 $111,111.00
311 Type 1 $544,852.00
311 Type 3 $54,897.00
922 Type 4 $95,187.00
922 Type 2 $175,844.00
311 Type 3 $247,544.00
457 Type 1 $9,875.00
311 Type 2 $10,079.00
922 Type 3 $600,457.00
311 Type 4 $578,155.00

I get a pivot similar to this

Emp # Type 1 Type 2 Type 3
Type 4
Count $ Count $ Count $
Count $
311 3 815716 1 10079 3 1254194 2 1235999
457 2 588334 1 9587 1 654789 1 987357
922 1 4578884 2 434300 2 1189357.5 1 95187

How do I use the calculated field to add Type 1 and 3 together?

Thanks so much!

In the pivot table, select one of the cells that has a Type heading
On the pivot table toolbar, choose PivotTableFormulasCalculated Item
Type a name for the calculated item, and in the formula box type:
='Type 1'+'Type 3'

This won't work if you have added a field to the pivot table twice. If
your source data only has three columns, as in your example, you'd need
another field that you can use to create the count.

dawn_dudley wrote:
Hi -

How do I make a formula in a pivot table based on the value of a column?
I have a pivot table with the results of totals by employee number of 4
types of loans, type 1, type 2, type 3 and type 4. I get the count and
amount of the loans by employee. I want to add type 1 to type 3 for each
employee and get a total count and total amount. I am stuck.

Example Data

Employee
Number Type Amount
457 Type 1 $578,459.00
922 Type 1 $4,578,884.00
922 Type 3 $588,900.50
311 Type 4 $657,844.00
457 Type 2 $9,587.00
457 Type 3 $654,789.00
311 Type 1 $159,753.00
922 Type 2 $258,456.00
311 Type 3 $951,753.00
457 Type 4 $987,357.00
311 Type 1 $111,111.00
311 Type 1 $544,852.00
311 Type 3 $54,897.00
922 Type 4 $95,187.00
922 Type 2 $175,844.00
311 Type 3 $247,544.00
457 Type 1 $9,875.00
311 Type 2 $10,079.00
922 Type 3 $600,457.00
311 Type 4 $578,155.00

I get a pivot similar to this

Emp # Type 1 Type 2 Type 3
Type 4
Count $ Count $ Count $
Count $
311 3 815716 1 10079 3 1254194 2 1235999
457 2 588334 1 9587 1 654789 1 987357
922 1 4578884 2 434300 2 1189357.5 1 95187

How do I use the calculated field to add Type 1 and 3 together?

Thanks so much!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra -

Worked perfectly! We are so happy, you made three of us so happy! I love
the microsoft community.

Dawn Dudley

"Debra Dalgleish" wrote:

In the pivot table, select one of the cells that has a Type heading
On the pivot table toolbar, choose PivotTableFormulasCalculated Item
Type a name for the calculated item, and in the formula box type:
='Type 1'+'Type 3'

This won't work if you have added a field to the pivot table twice. If
your source data only has three columns, as in your example, you'd need
another field that you can use to create the count.

dawn_dudley wrote:
Hi -

How do I make a formula in a pivot table based on the value of a column?
I have a pivot table with the results of totals by employee number of 4
types of loans, type 1, type 2, type 3 and type 4. I get the count and
amount of the loans by employee. I want to add type 1 to type 3 for each
employee and get a total count and total amount. I am stuck.

Example Data

Employee
Number Type Amount
457 Type 1 $578,459.00
922 Type 1 $4,578,884.00
922 Type 3 $588,900.50
311 Type 4 $657,844.00
457 Type 2 $9,587.00
457 Type 3 $654,789.00
311 Type 1 $159,753.00
922 Type 2 $258,456.00
311 Type 3 $951,753.00
457 Type 4 $987,357.00
311 Type 1 $111,111.00
311 Type 1 $544,852.00
311 Type 3 $54,897.00
922 Type 4 $95,187.00
922 Type 2 $175,844.00
311 Type 3 $247,544.00
457 Type 1 $9,875.00
311 Type 2 $10,079.00
922 Type 3 $600,457.00
311 Type 4 $578,155.00

I get a pivot similar to this

Emp # Type 1 Type 2 Type 3
Type 4
Count $ Count $ Count $
Count $
311 3 815716 1 10079 3 1254194 2 1235999
457 2 588334 1 9587 1 654789 1 987357
922 1 4578884 2 434300 2 1189357.5 1 95187

How do I use the calculated field to add Type 1 and 3 together?

Thanks so much!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html