How about something like:

=SUMPRODUCT(1/COUNTIF(Data!$A$2:$A$24,Data!$A$2:$A$24)*(Data!$B$ 2:$B$24=Pivot!B6))

in E6 (copied down), and something like:

=COUNTIF(Data!$C$2:$C$24,Pivot!G7)

in J7 (copied down)

HTH

"Thomas Mueller" wrote:

Hi,

I've some problems to count unique items (Invoice #) in a pivot table. There
is the default solution "Add-a-new-calculated-column" as mentioned on
http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't
works.

My problem is, that these values aren't in a Excel worksheet; it's a
external data source - a SQL-Select via DAO/ADO. Because of that, I don't
have the possibility to add a new column. Another reason is, that the pivot
table should always be dynamic: Group over this field, group over another
field - and always show the number of unique invoices, not the sum of data
rows (please take a look at the Excel sheet:
http://e-tom.ch/Count_Unique_Invoice_No.xls).

Thx, Thomas

(or is there an easy way to write an User Definied Function and access to
the data rows in each pivot table group?)