counting in pivot table

[Guest]

I have the data source like below:
product customer quantity
1 A 1000
2 A 1000
1 B 500
3 B 200

and my pivot table like below:
customer product total
A 1 1000
2 1000
A Count 2
B 1 500
3 200
B Count 2

How can I get the number of customer = 2 (A, B)?
and number of product = 3 (1, 2, 3)?
It seems to me that the count on pivot table only count the number of row of
that data.

Can I get this count from pivot table or other excel formula?

 

[Max]

One way ..

Assuming this table is in Sheet1, cols A to C
data from row2 down

product customer quantity
1 A 1000
2 A 1000
1 B 500
3 B 200

In Sheet2
-------------
With the labels in A1:B1 : UniqueProd, UniqueCust

Put in A2:

=SUM(IF(LEN(Sheet1!A2:A100)>0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))

Array-enter the formula with CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy A2 across to B2

A2 and B2 will return the number of unique items
in col A and B in Sheet1

Adjust the ranges to suit

 

[Guest]

That's great, even though I don't understand how this formula work
Thanks a lot!
Marisa

 

[Max]

You're welcome!

 

[Guest]

Hi Max,
I'm also interested to know how to construct ur prev. formula:
=SUM(IF(LEN(Sheet1!A2:A100)>0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))

juz dont understand how the part works? ~
1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)

thx!

 

[Max]

I'm also interested to know how to construct ur prev. formula:
=SUM(IF(LEN(Sheet1!A2:A100)>0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))

juz dont understand how the part works? ~
1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)

ok, here's an attempt to explain
(others could do this much better <g>)

Based on the sample data,
A2:A100 in Sheet1 contains:

1
2
1
3
<rest are blanks>

For the sample data, the part: LEN(Sheet1!A2:A100)>0
will resolve to: {TRUE;TRUE;TRUE;TRUE;FALSE;...FALSE}

The IF(LEN(Sheet1!A2:A100)>0 will "pick" only the parts in the return from:
1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)
corresponding to TRUE,
i.e. it'll pick the first 4 values in the return from the 1/COUNTIF(...)
(**bear this in mind)

COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)
compares each cell in A2:A100 in Sheet1
to each of the other cells in the same range and
returns an array containing counts

The return from COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) would be:

2
1
2
1
<rest are zeros>

as there are two 1s, one 2 and one 3
in A2:A100 in Sheet1

So 1/COUNTIF(...) would return

0.5 (=1/2)
1 (=1/1)
0.5 (=1/2)
1 (=1/1)
<rest are #DIV/0!> (=1/0)

As only the first 4 values in the return from the 1/COUNTIF(...)
will be picked (see ** above), hence the expression:

SUM(IF(LEN(...)>0,1/COUNTIF(...)))

would ultimately resolve to : 0.5 + 1 + 0.5 + 1 = 3
which is the number of uniques in the range

 

[Guest]

its great! thx!

ok, here's an attempt to explain
(others could do this much better <g>)

Based on the sample data,
A2:A100 in Sheet1 contains:

1
2
1
3
<rest are blanks>

For the sample data, the part: LEN(Sheet1!A2:A100)>0
will resolve to: {TRUE;TRUE;TRUE;TRUE;FALSE;...FALSE}

The IF(LEN(Sheet1!A2:A100)>0 will "pick" only the parts in the return from:
1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)
corresponding to TRUE,
i.e. it'll pick the first 4 values in the return from the 1/COUNTIF(...)
(**bear this in mind)

COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)
compares each cell in A2:A100 in Sheet1
to each of the other cells in the same range and
returns an array containing counts

The return from COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) would be:

2
1
2
1
<rest are zeros>

as there are two 1s, one 2 and one 3
in A2:A100 in Sheet1

So 1/COUNTIF(...) would return

0.5 (=1/2)
1 (=1/1)
0.5 (=1/2)
1 (=1/1)
<rest are #DIV/0!> (=1/0)

As only the first 4 values in the return from the 1/COUNTIF(...)
will be picked (see ** above), hence the expression:

SUM(IF(LEN(...)>0,1/COUNTIF(...)))

would ultimately resolve to : 0.5 + 1 + 0.5 + 1 = 3
which is the number of uniques in the range

 

[Max]

You're welcome !
Thanks for posting back

 

[Guest]

I have been struggling with this same problem, but I need to find a solution
that works in a Pivot Table calculated field...

I am using a Pivot Table to view data from a very large database. The Pivot
Table has the following fields: Customer, Quarter and Sales. There are many
duplicate Customer sales in the database.

I would like to create a Pivot Table that calculates the # of unique
customers in each quarter. If I use the Pivot Table Count function it double
counts all of the duplicate Customer entries. Can I create a Calculated Field
that accomplishes this?

I have tried using the formula you provided,
“=SUM(IF(LEN(Sheet1!A2:A100)>0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))â€,
but since I am using it in a Pivot Table calculated field I used the
following format: “=SUM(IF(LEN(Customer)>0,1/COUNTIF(Customer, Customer)))â€.
Where “Customer†is inserted from the Pivot Table field list. I get a “The
function contains an error†message.

Can you help me understand what I am doing wrong?

Thanks, Mike

 

[Max]

Can you help me understand what I am doing wrong?

The suggested formula is an array formula,
and from Excel Help (xl97) for calculated field syntax:
".. you cannot use array functions. "

But you could try using the formula directly on the source data set
using a dynamic range (Cust) for the customers

Suppose the source data is in Sheet1,
with Customers listed in col B, B2 down
(the listing in col B is assumed continuous,
without any in-between blank rows)

Click Insert > Name > Define
Under Names in workbook, enter: Cust
In the "Refers to" box, put:
=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1,)
Click OK

(The above will create a dynamic range: Cust)

In Sheet2 (say),
copy and paste into the formula bar for say, B2:

=SUM(IF(LEN(Cust)>0,1/COUNTIF(Cust,Cust)))

Remember to array-enter the formula
by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

(if you don't array-enter, you'll get the error #VALUE!)

B2 should return the unique counts of the customers in col B of Sheet1

And if the counts are out, the problem probably lies with the source data
(not clean)

You could try using TRIM() to clean up any extraneous white spaces (not
apparent)

E.g.: In Sheet1, use an empty col to the right,
say col X?, put in X2: =TRIM(B2)
copy down, then copy col X and
overwrite col B with a paste-special > values > ok

 

[Guest]

Thanks Max, but I don't have direct access to the source data. I need to find
a way to do it with the Pivot Table Calculated Field. Is there no way to do
this? Seems like getting a unique count would be a common thing to want to do
with Pivot Tables.

Mike

 

[Max]

Sorry, Mike. I've no further comments to offer you.
Hang around awhile for possible insights from others.

 

[Guest]

Hi Mike,
Today I find a work around if you only need the unique count of the first
column of your pivot. Actually it is a basic function in excel. There is
a auto-calculation result on the left bottom on excel screen, which provide
auto-calculation of SUM, AVG, COUNT....
First, you change the auto-calculation to COUNT. Select the range on the
first column, it gives the unique count on the auto-calculation result.
Hope this help!
Marisa

 

[Guest]

I have a similar question, except, I need to count unique instances where
there is another variable. Sample Data:

Program Student University
1 John North University
1 Steven North University
1 James South University
2 Susie Case University
2 Laura West University
2 Lisa North University

So, in this sample data, I would need to count the number of unique
universities per program, that is, Program 1 - 2 univeristies, program 2 - 3
universities.

Additionally, I need to ensure that the formula updates upon opening, since
the data set is in another sheet, and will be updated frequently.

 

[Guest]

Marisa -

FWIW, I've gotten fairly good at this Access Pivot Table stuff, and I
haven't found a way to do it. If no one else has either, maybe MS should add
this capability to the next Access version!

Kevin