0

I have found many options to count unique text values in an Excel column. But nothing on how to do it in a spreadsheet formatted as a table. The formulas I have seen so far counts all the rows regardless of the filtering. I want to be able to count only the rows resulting after I filter the table. To explain better, my table has over 100 rows; but only 27 unique values. The formula will always show 27 even when I filter data on another column. Any ideas?

1 Answer 1

0

Use this Array Formula:

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Table1[[#Headers],[Column 2]],ROW($ZZ$1:INDEX($ZZ:$ZZ,COUNTA(Table1[Column 2]))),0,1,1)),MATCH(Table1[Column 2],Table1[Column 2],0)),ROW($ZZ$1:INDEX($ZZ:$ZZ,COUNTA(Table1[Column 2]))))>0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

It uses Subtotal to iterate the rows to test if visible or not.

enter image description here

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .