Share via

Count & Subtotal unique items in a column

Anonymous
2013-08-09T15:24:51+00:00

I have a column in Excel that I would like to not only count the number of unique names in, but I would also like to SUBTOTAL based on filtered data.

As you can see in 'SCREENSHOT 1' below, I have the function to count the unique instances. in cell C19 however in 'SCREENSHOT 2', I need to find a way to have that value only show the filtered information like the SUBTOTAL function does in Cell E20.

Pivot tables are not an option for this spreadsheet so I do need to find a way to accomplish this with the displayed rows.  If I have to build out hidden columns to the right of this work area, I have no objection to adding this to obtain the end goal.

SCREENSHOT 1

SCREENSHOT 2

Thank you!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-08-09T16:03:45+00:00

    Try this array formula,

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C21,ROW(1:15),0)),MATCH(C22:C36,C22:C36,0)),MATCH(C22:C36,C22:C36,0)),1))

    Note that there are 15 rows in the C22:C36 (inclusive) and that we start the array processing at C21 since we are offsetting a minimum of 1 row.

    4 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-08-09T17:20:11+00:00

    This worked perfectly!!

    Thank you!!

    0 comments