# How can I count unique items in a filtered column?

1. ## How can I count unique items in a filtered column?

Hi,

I have a column with patients names like this:

smith
taylor
taylor
taylor
garcia
garcia
williams
brent

I need to count the duplicates only once though (to equal 5 in this example). It's a pretty massive spreadsheet I'll be applying different filters to. I'm not advanced in Excel, so I'll need it explained in lamen's terms.

Terry

2. ## Re: How can I count unique items in a filtered column?

Refer attached.

The count is displayed in Column B and total no. of unique names is in Cell C1

3. ## Re: How can I count unique items in a filtered column?

Without the helper column

To count all unique records

=SUM(IF(A2:A9<>"",1/COUNTIF(A2:A9,A2:A9)))

To count filtered unique records

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A10)-ROW(A2),0)),MATCH(A2:A10&"",A2:A10&"",0)),MATCH(A2:A10&"",A2:A10&"",0)),1))

Note that these are both array formula and must be confirmed using Shift Ctrl Enter.

4. ## Re: How can I count unique items in a filtered column?

count with pivot table without helpcell (see the attachment).

5. ## Re: How can I count unique items in a filtered column?

Thank you so much for your responses! I'll definitely try these out today and write back on which one I end up using.

6. ## Re: How can I count unique items in a filtered column?

Originally Posted by jason.b75
Without the helper column

To count all unique records

=SUM(IF(A2:A9<>"",1/COUNTIF(A2:A9,A2:A9)))

To count filtered unique records

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A10)-ROW(A2),0)),MATCH(A2:A10&"",A2:A10&"",0)),MATCH(A2:A10&"",A2:A10&"",0)),1))

Note that these are both array formula and must be confirmed using Shift Ctrl Enter.
Okay. I have a few questions. Like I said, I'm still learning. How much of this information will I be changing to fit my table?
Would I have to alter the formula for filtered data each time i apply a different filter?

7. ## Re: How can I count unique items in a filtered column?

I seem to have gotten this to work. Is there any way I could use this to work within filters?

=SUMPRODUCT(1/COUNTIF(R[-1773]C[-21]:R[-3]C[-21],R[-1773]C[-21]:R[-3]C[-21]))

8. ## Re: How can I count unique items in a filtered column?

why the rc format?

9. ## Re: How can I count unique items in a filtered column?

Originally Posted by terry_believers
Would I have to alter the formula for filtered data each time i apply a different filter?
The second formula from my previous post will only count the rows still visible after the filter is applied and will recalculate when you change the filter criteria.

To apply to your table, A2:A10 should be changed to refer to your entire range of data, A2 should be the first cell.

Using the R1C1 notation from your formula:-

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(R[-1773]C[-21],ROW(R[-1773]C[-21]:R[-3]C[-21])-ROW(R[-1773]C[-21]),0)),MATCH(R[-1773]C[-21]:R[-3]C[-21]&"",R[-1773]C[-21]:R[-3]C[-21]&"",0)),MATCH(R[-1773]C[-21]:R[-3]C[-21]&"",R[-1773]C[-21]:R[-3]C[-21]&"",0)),1))

Remember to array confirm the formula with Shift Ctrl Enter.

10. ## Re: How can I count unique items in a filtered column?

I suppose that's my default setting. Is there a quick way to change that?

11. ## Re: How can I count unique items in a filtered column?

ALT > F > I > F > Working with Formulas > R1C1 Reference Style (uncheck this option)

12. ## Re: How can I count unique items in a filtered column?

Fantastic! I've changed my settings to match the A2:A10 format and I've inputted that formula to count filtered unique records. It works like a charm. Thank you guys SO much for your help!

There are currently 1 users browsing this thread. (0 members and 1 guests)