Count unique values - Pivot Table

[Thomas Mueller]

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

[Gary76]

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

[Thomas Mueller]

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" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> 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]

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" <[email protected]> schrieb im Newsbeitrag
> news:[email protected]...
>
>>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