Count unique values for filtered column

[drom]

Hi and Thanks in advance!

-If I wanna count the unique values in a table column, I can Use:

• =SUMPRODUCT(1/COUNTIF(rcTableColumn, rcTableColumn))
  • where rcTableColumn is a named range for this table column

But I would like to use the SumProduct to count the unique values for the visible cells only.

I am using a column where If the row is hidden the value =0 so In this column I can use this formula:

• =SUMPRODUCT(--(TbRowData[RowVisible]<>""))
  • If the cell are visible this column puts "YES")
  • To count how many cells are visible in a filtered table

How can I combine both SumProduct formulas, kind of:

• =SUMPRODUCT(--(TbRowData[RowVisible]<>""),--(1/COUNTIF(rcTableColumn;rc,TableColumn)))
  • But tis formula does not work

I do not want to Use UDF, Not Macros
I am not using 365

 

[myall_blues]

Try this formula as shown in B15 - note that this is an array formula so you need to enter it using CTRL+SHIFT+ENTER, not just ENTER. I think it's easier to understand as ranges but you can convert to named ranges. Note also that there are 12 rows in A2:A13 (inclusive) and that we start the array processing at A1 since we are offsetting a minimum of 1 row.

Regards

Murray

Book1
	A	B
1	Number	Filter
2	1	YES
3	2	YES
4	3	YES
5	4	YES
6	1	YES
7	2	YES
8	3	YES
9	4	YES
10	1	YES
11	2
12	3
13	4
14
15	Unique values in col A	4
PhoneList

Cell Formulas
Range	Formula
B15	B15	=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A1,ROW(1:12),0)),MATCH(A2:A13,A2:A13,0)),MATCH(A2:A13,A2:A13,0)),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Number	Filter
2
3
4
Unique values in col A	3