#### theta

##### Well-known Member
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...

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### theta

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Well-known Member
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

Last edited:

#### RoryA

##### MrExcel MVP, Moderator
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

##### Well-known Member
All sorted, thanks!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,673
Messages
5,838,710
Members
430,566
Latest member
ChanchalSingh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back