Count Unique Cells in Table BEFORE and AFTER filters have been applied

[ajoshi87]

I have 800 names in a table along with several filtering criteria that will be applied. What I need to know is the number of unique names in the table in the beginning and after a set of filters have been applied. Example: There are 300 unique names, I apply the filter in the table "males only" and the counter automatically updated to 150 names, i then apply the filter "starts with A" and the counter automatically updates again.

Just for background here are some of the things I've tried:

1. Pivot Table: this is no good because it needs to automatically update when a filter is applied and I can't use macros because it is not best practice (unfortunately)

2. IF(AND(COUNTIF statement: the exact statement I used was : =IF(AND(COUNTIF($A$1:$A$1000,A2)>0,COUNTIF($A$1:A1,A2)=0),1,0). The problem with this is that once rows get filtered because of later criteria, the formula does not adjust to which row is next in the table but rather which row
was there before the filtering occurred.

3. Advanced Filter- Unique Values Only: I can't change the original list but when I copy values to another location and then apply my filtering criteria, that copied location doesn't update to what names are left in the table.


If anyone has any method they think could work that would be great! If macros are the only way to do it that information would help too but hopefully that is not the case.

Thanks!

 

[barry houdini]

Welcome to MrExcel ajoshi87,

Try this "array formula"

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A1,ROW(A1:A1000)-ROW(A1),0)),MATCH(A1:A1000,A1:A1000,0)),ROW(A1:A1000)-ROW(A1)+1),1))

needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

 

[ajoshi87]

@barry, I actually did try an array formula but unfortunately since there are 900-something rows, the file has a tendency to freeze every time something is filtered because the array is being updated. I tried a few times on several different computers and had the same problem each time.

Thank you for the suggestion though!

 

[barry houdini]

OK, perhaps use a helper column - e.g. if column H is free put this formula in H1 copied down

=IF(SUBTOTAL(3,A1),A1,"")

Then use this formula for a unique count

=SUMPRODUCT((H1:H1000<>"")/COUNTIF(H1:H1000,H1:H1000&""))

 

[loudnoiseman]

Hi Barry & All,

Barry, your array formula is just what I need to count uniques only horizontally across rows instead of down a column - for example G2:OP2. I tried to adapt this array with the following but it doesn't work: =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(G2,COLUMN(G2:OP2)-COLUMN(G2),0)),MATCH(G2:OP2,G2:OP2,0)),COLUMN(G2:OP2)-COLUMN(G2)+1),1))

don't laugh...

Any suggestions appreciated!!

 

[Aladin Akyurek]

Hi Barry & All,

Barry, your array formula is just what I need to count uniques only horizontally across rows instead of down a column - for example G2:OP2. I tried to adapt this array with the following but it doesn't work: =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(G2,COLUMN(G2:OP2)-COLUMN(G2),0)),MATCH(G2:OP2,G2:OP2,0)),COLUMN(G2:OP2)-COLUMN(G2)+1),1))

don't laugh...

Any suggestions appreciated!!

You probably just need...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(G2:OP2<>"", MATCH("~"&G2:OP2,G2:OP2&"",0)),
    COLUMN(G2:OP2)-COLUMN(G2)+1),1))

 

[loudnoiseman]

Brilliant!! Thank you Aladin

 

[Aladin Akyurek]

Brilliant!! Thank you Aladin

You are welcome. Thanks for providing feedback.

Also good to know...

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[T. Valko]

Hi Barry & All,

Barry, your array formula is just what I need to count uniques only horizontally across rows instead of down a column - for example G2:OP2. I tried to adapt this array with the following but it doesn't work: =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(G2,COLUMN(G2:OP2)-COLUMN(G2),0)),MATCH(G2:OP2,G2:OP2,0)),COLUMN(G2:OP2)-COLUMN(G2)+1),1))

don't laugh...

Any suggestions appreciated!!

I've never had to do a unique count on a row in a filtered list.

I'm thinking this would be very difficult to do. However, it would be very easy to use an additional column that gets the unique count for each row.

If your range is G2:OP2 then in OQ2 enter this formula:

=SUMPRODUCT((G2:OP2<>"")/COUNTIF(G2:OP2,G2:OP2&""))

Copy down as needed.

 

[T. Valko]

I've never had to do a unique count on a row in a filtered list.

I'm thinking this would be very difficult to do. However, it would be very easy to use an additional column that gets the unique count for each row.

If your range is G2:OP2 then in OQ2 enter this formula:

=SUMPRODUCT((G2:OP2<>"")/COUNTIF(G2:OP2,G2:OP2&""))

Copy down as needed.

P.S.

If there won't be any empty cells in the range then it's even easier:

=SUMPRODUCT(1/COUNTIF(G2:OP2,G2:OP2))