Pivot table formulas

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?
 
J

JulieD

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
 
A

Alan Bentley

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?
 
D

Debra Dalgleish

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.
 
Top