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

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

Jep, first formula works correctly, thx! But I'd like to have something like
a pivot field - gives the clients the possibility to add this field to the
pivot table with drag'n'drop - without a "hack".

I'll write a User Defined Function and put it in a calculated field, the
only way to solve this problem (in my eyes).
A lot of people have been having this problem for years - but there is no
Excel built-in function... Looks like nobody of the Excel Dev Team cares
about...

Thx, Thomas


"Gary76" schrieb im Newsbeitrag
...
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?)

You won't be able to use a User Defined Function in a pivot table's
calculated field.

Thomas Mueller wrote:
Jep, first formula works correctly, thx! But I'd like to have something like
a pivot field - gives the clients the possibility to add this field to the
pivot table with drag'n'drop - without a "hack".

I'll write a User Defined Function and put it in a calculated field, the
only way to solve this problem (in my eyes).
A lot of people have been having this problem for years - but there is no
Excel built-in function... Looks like nobody of the Excel Dev Team cares
about...

Thx, Thomas


"Gary76" schrieb im Newsbeitrag
...

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









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