# Pivot Table - How do I get unique counts of Grand Totals?

#### Paul M

##### New Member
Hello Guys:

This one has been baffling me: How can I use the data below to generate the (manually adjusted) pivot table shown below it? The unique counts should work for any combination of firm and state selected from the drop-down list. Thanks!
Book2.xls
BCDEFGHI
1Data:
2IDStateFirm
31000NYCitigroup
41001NYLehman
51001NJGoldman
61002CAGoldman
71002DCMerrill
81003NJBearStrn
91003FLJPM
10
11
12DesiredPivotTable
13CountofIDState
14FirmCADCFLNJNYMultipleCodesGrandTotal
15BearStrn11
16Citigroup11
17Goldman112
18JPM11
19Lehman11
20Merrill11
21MultipleCodes-3-3
22GrandTotal11122-34
Sheet1

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Juan Pablo González

##### MrExcel MVP
Re: Pivot Table - How do I get unique counts of Grand Totals

You need to add (at least) one extra column...
Book1
ABCDEFG
1IDStateFirmTempUnique
21000NYCitigroup1000NY1
31001NYLehman1001NY1
41001NJGoldman1001NJ1
51002CAGoldman1002CA1
61002DCMerrill1002DC1
71003NJBearStrn1003NJ1
81003FLJPM1003FL1
9
10
11
12
13
14CountofIDState
15FirmCADCFLNJNYGrandTotal
16BearStrn11
17Citigroup11
18Goldman112
19JPM11
20Lehman11
21Merrill11
22GrandTotal111227
Sheet1

D2 equals:

=A2&CHAR(127)&B2&CHAR(127)

and E2:

=1/SUM(COUNTIF(\$D\$2:\$D\$8,D2))

#### Paul M

##### New Member
Re: Pivot Table - How do I get unique counts of Grand Totals

Juan,

I want to get a grand total of 4. How can I do that. In my estimation I can only get that by using the sum function instead of the count and include somehow a column of negative multiples. That's the way I have reasoned it out in my head, but I'm not sure how practical it is to achieve.

#### Andrew Poulsom

##### MrExcel MVP
Re: Pivot Table - How do I get unique counts of Grand Totals

Like this?
BOOK13
ABCDEFGHIJKLMN
1IDStateFirmUniqueDupesSumofUniqueState
21000NYCitigroup1-FirmCADCFLNJNYMultGrandTotal
31001NYLehman1-BearStrn11
41001NJGoldman1(1)Citigroup11
51002CAGoldman1-Goldman112
61002DCMerrill1(1)JPM11
71003NJBearStrn1-Lehman11
81003FLJPM1(1)Merrill11
9MultMult(3)Mult(3)(3)
10GrandTotal11122(3)4
Sheet1

The formula in E2 is:

=IF(COUNTIF(A\$2:A2,A2)=1,0,-1)

copied down.

The formula in D9 is:

=SUM(E2:E8)

#### Paul M

##### New Member
Re: Pivot Table - How do I get unique counts of Grand Totals

That will work just fine for the data as shown, but how can I make it work for any combination of firm and state?

The real data has about 10,000 records of which say 3,000 are dupes. Also the real data has not 2 but 8 fields, and it should work for any combination as well. I also do a pivot chart on the data.

Thx

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.

Threads
1,168,089
Messages
5,857,297
Members
431,869
Latest member
Avinashz

### 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

### 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