Add count to pivot

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
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...
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
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

pivot.JPG
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,168
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 9, 2009
Messages
960
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
Joined
May 2, 2008
Messages
36,168
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,200
Members
414,513
Latest member
junbuggle

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top