can you unique count in a pivot table??

[pkhoo]

For example,
region Cust id Product
A 21 a1
A 21 b2
B 21 a1
B 21 b2

I would like to see

region count of cust
A 1
B 1

thank you in advance

 

[Zathras]

You can extract the region and cust id columns (in this example) to a separate range and then pivot that range. See Data/Filter/Advanced Filter... for the way to copy unique records from one range to another (e.g., you can copy unique rows from A1:B5 into E1)

I don't know of a way to do it directly within a Pivot Table. Perhaps someone else here does.

 

[onedtent]

just to repeat what zathras said, no direct way to do it.

if u put region and cust id in row headers, u can count the number of rows it generates

 

[macrus]

In your data range simply create a counter column for the variable you need to count such as this:

IF(A2=A1,0,1) This will work for all records from row 2 onwards. Simply hard-code row 1 with a 1.

A Pivot Table can then simply sum up this column to give a unique count.

 

[ETID]

In your pivot table layout...

drag...

"Region" field to ROW

"Product" field to Column...or to Page (It's your call how you want to display)

"Cust ID" to Data...and set to summarize by count