# SUBTOTAL Unique Records

#### BettyKat

##### New Member
Hi,

I would like to have a formula existing the will tell me the count of unique records when filtered.

Data represented below.

ID Name Priority

ORPAU0092 MONTICELLO UH
ORPAU0092 MONTICELLO UH
ORPAU0203 LONGVIEW NORTH UH
ORPAU0203 LONGVIEW NORTH UH

In the above example if I filtered for ORPAU0092 I would like the count to show 1.

Current formula being used is =CONCATENATE("Count of Sites - ",VALUE(SUBTOTAL(2,X3:X587))) but this does not account for unique records.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### hkaplan2

##### Active Member
You can do it with a pivot table.

In the PT layout screen, drag the ID, name and priority fields to the row area and the ID (or any) field to the data area.

Double click each field in the row area and select None for subtotals.

Double click the ID field in the data area and select Summarize by Count.

In the same dialog box, Click the Options button and select Index in the "Show Data As" field.

In the wizard Options dialog, uncheck Grand Totals.

Click Finish.

This PT will give you a 1 for each unique ID/Name/Priority combo. Simply add the column and you get a count of unique records.

##### MrExcel MVP
Control+shift+enter, not just enter...
Code:
``````="Count of Sites - "&
SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(X3,ROW(X3:X587)-ROW(X3),,1)),
IF(X3:X587<>"",MATCH("~"&X3:X587,X3:X587&"",0))),ROW(X3:X587)-ROW(X3)+1),1))``````

#### BettyKat

##### New Member
Thank you both for responging, it is much appreciated.

Thank you for the pivot option, but I would not like to have a pivot in my file as this would increase the size too much.

When I entered the formula provided and updated the arrays as needed, confirming with a contol+shift+enter the filed is just displaying what I have written into it verses providing a result. Is this related to me working in 2003? It acts like it recognizes the formula but it not responging as anticipated.

Any thoughts?

##### MrExcel MVP
Thank you both for responging, it is much appreciated.

Thank you for the pivot option, but I would not like to have a pivot in my file as this would increase the size too much.

When I entered the formula provided and updated the arrays as needed, confirming with a contol+shift+enter the filed is just displaying what I have written into it verses providing a result. Is this related to me working in 2003? It acts like it recognizes the formula but it not responging as anticipated.

Any thoughts?

Are you saying that the formula cell shows the formula itself, not its result?

If so, set the format of the formula cell to General and hit F2.

#### BettyKat

##### New Member
Yes - I changed the format to general and then hit F2, then cntl+shift+enter, then it recognized as a formula, but showed an error related to the row portion of the formula...

I am looking into this and will post after a little troubleshooting.

#### BettyKat

##### New Member
I had misentered the row ranges that I was referencing, formula works perfectly

#### mickmac

##### New Member
I used this after autofiltering a list... ?
numrow = Application.WorksheetFunction.Subtotal(3, Worksheets("Sheet1").Range("A5:A3000"))

Replies
5
Views
91
Replies
3
Views
116
Replies
12
Views
616
Replies
5
Views
309
Replies
3
Views
615

1,195,632
Messages
6,010,788
Members
441,569
Latest member
PeggyLee

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