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" wrote:

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Marisa" wrote in message
...
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?