Pivot table formulas

  • Thread starter Thread starter Alan Bentley
  • Start date Start date
A

Alan Bentley

Hi,

I am trying to create a calculated field in a pivot table but cannot get the
expression to work correctly.

I have a field called Class Description and I need to perform a COUNT
function on it. I have entered =COUNT('''Class Description''') as the
formula but Excel always returns 1.

Does anyone have any ideas how I can resolve this please?
 
Hi Alan

if its a text field then excel will automatically "count" it when you drag
it into the data area - or am i completely misunderstanding what you're
after.

Cheers
JulieD
 
Thanks Julie,

I understand what you say but my question is a little deeper...

I need to be able to multiply a Pivot table field by the number of items
that have been consolidated. E.g. I have different items each with its own
class description and I have a column in the PT that performs a count of
those so I can see how many there are. But I cannot find a way to use this
value in a formula to multiply another column. I thought that = '''Cost'''
* COUNT('''Class Description''') would do it but that formula resolves
COUNT('''Class Description''') to be always 1.

Any ideas?
 
It's not clear to me exactly what you're trying to do, but you could add
a column to the database, then add that field to the pivottable.

For example, if you want to count the customers in column A, use the
following formula in row 2:
=IF(COUNTIF(A$2:A2,A2)=1,1,0)

Copy this formula down to all rows in the database.

Or, to count all customer records, enter a 1 in each record in the column.

Add this field to the pivot table data area, as a Sum, and you'll get a
count of unique items, or a count of records. You could multiply this
field in your formula.
 
Back
Top