=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15))

if there are no empty cells in the specified. range.

--
Regards,
Tom Ogilvy


Fred Smith wrote in message
...
Thanks Debra,

I thought of that, and it would solve part of problem. But I'm really
looking for the number of clients in each category. In the source data
table, a client could have 6 different accounts. I could use your
suggestion
to categorize them, but how do I count them? If I count in a pivot table
from the original data source, it's going to use 6 rather than 1. Is it
possible to count the unique entries in a data source?

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Debra Dalgleish" wrote in message
...
Instead of creating another pivot table, you could calculate the
category in the data source table.

In the data source table, add a column in which you use SUMIF, or
SUMPRODUCT to total the assets for each customer. In another column, use
a VLOOKUP formula to calculate the category.

Add the category field to the pivot table.

Fred Smith wrote:
I'm trying to create a pivot table from an existing pivot table.

My pivot table is in the range of A1:N484. It's a list of clients and
their
total assets. I've added column O which categorizes each client based
on
their assets. Now I want a pivot table by category.

My code is:

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
Sheets("Households").Range("A2", "O483").Address)
Set PT = PTCache.CreatePivotTable(TableDestination:="", _
TableName:="AssetLevels")

But VBA gives me an error 1004 at the "Set PT" statement. PTCache is
defined
as a PivotCache, and PT is a PivotTable. If it makes any difference,
the
same variables were use to create the original pivot table.

I find VBA help frustrating, because when I lookup the Add method of
PivotCaches, it says the first parameter is the type of data and can
be
xlConsolidation, xlDatabase, xlExternal, xlPivotTable or xlScenario.
But
it
doesn't say where each option applies. I know I don't have xlEternal,
but it
might be one of the others. Is that my problem?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html