Add count to pivot

[theta]

Hi

I have a field on my pivot table called TYPE

This is in the row label, so it splits my data between SINGLE and GROUP

The next row label is NO (effectively the reference)

How can I produce a unique count of references?

GROUP
....4
....6
....9

SINGLE
...1
...2
...3
...5
...7
...8

Need a total COUNT for GROUP to = 3 (there are 3 unique NO)

Need a total COUNT for SINGLE to = 6 (there are 6 unique NO)

Any use of count that I introduce just shows the count of NO in the source data (so for GROUP it is multiple, for single it is 1)

Any help appreciated

Can use calcualted column etc...

 

[theta]

Here is a pic, need a count of the NO by TYPE

OPEN, GROUP would =3
OPEN, SINGLE would =4 (already there)
SETTLED, GROUP would =0
SETTLED, SINGLE would =2 (alreaady there)

It is the GROUP that I am struggling as the built in COUNT is just the count of occurences of that ref in raw data, not the unique count

 

[RoryA]

You'll need to add a formula to the source data to do this. You can't do it in the pivot table itself.

 

[theta]

What would be the best way to do this?

1/COUNTIF ?

The most important thing for me is calculation speed. I am using an i7 and SSD, but some poor folks in the office are still using pentium 4's

 

[RoryA]

Yes, 1/COUNTIF but you'll need to make a key column concatenating your 3 rowfields so you get a unique count for that specific combination.

 

[theta]

All sorted, thanks!