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
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.
