How to count unique alhpanum values in a filtered column ignoring blank cells

[jael36]

Hi All,

I think the title says it all...

In a given column I may have duplicate values (i.e.: someones initials).
These values may be in multiple cells in that same column.
I have filters set on this column so I can quickly find the tasks associated to those initials per row.
Unfortunately, I have rows that don't have any initials and are left blank.

What I would like to do is be able to have a count of the number of unique initials/people in that column when a)unfiltered b)filtered.

I am using the following formula but it provides the wrong count (haven't quite figured out how the value is being calculated as it is).

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B6,ROW(B6:B106)-ROW(B6),0)),MATCH(B6:B106,B6:B106,0)),MATCH(B6:B106,B6:B106,0)),1))}

I've attached a screenshot of the type of data I have.
Let me know if you need an excel to work with and I will have to clean it up a bit.https://drive.google.com/folderview?id=0B_ypyEjzfat2QlVlT3JaZkZJalE&usp=sharing

Thanks for your help!
J

 

[circledchicken]

Hi and welcome to the forum,

Perhaps try the following (using CTRL-SHIFT-ENTER rather than just ENTER as these are array formulae):

Unfiltered:

=SUM(
    IF(
      FREQUENCY(
        IF(
          B6:B106 <> "",
          MATCH("~" & B6:B106, B6:B106 & "", 0)),
        ROW(B6:B106) - ROW(B6) + 1),
      1))

Filtered:

=SUM(
    IF(
      FREQUENCY(
        IF(B6:B106 <> "",
        IF(
          SUBTOTAL(3, OFFSET(B6, ROW(B6:B106) - ROW(B6), 0)),
          MATCH("~" & B6:B106, B6:B106 & "", 0))),
        ROW(B6:B106) - ROW(B6:B106) + 1),
      1))

 

[jael36]

Hi @circledchicken, sorry for the delayed reply.

So that seemed to help, but it seems to be counting the filters to a max count of 2.

so if i put a filter on a column, I should count 6 unique visible values (excluding blanks of course), but the count is 2.
this happens on any column

thoughts?

 

[circledchicken]

Hi,

That shouldn't happen. Make sure you press CTRL-SHIFT-ENTER rather than just ENTER to confirm the formulae.

If it still doesn't work please provide a small sample dataset showing the result you are getting vs the expected result.