SUBTOTAL Unique Records

[BettyKat]

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.

Any ideas? Thanks in advance.

 

[hkaplan2]

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.

 

[Aladin Akyurek]

Control+shift+enter, not just enter...

="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]

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?

 

[Aladin Akyurek]

ADVERTISEMENT

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]

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.

Thanks for your help!!

 

[BettyKat]

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

 

[mickmac]

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