Count Unique Values visible in a filtered table

0

I need to count the nuber of unique values visible in a filtered table.

I can count the visible values in a filtered list with the formula

"=SUBTOTAL(3,Table3[W/O'#])"

And count the unique values in the table with the array formula

"{=SUM(1/COUNTIF(E9:E347,E9:E347))}"

But haven't been successful in combining them.

Anyone have a solution to Counting Unique Values visible in a filtered table?

Answer
Discuss

Answers

0
Selected Answer

Here is a pretty gnarly formula. It currently assumes the headers are in row 1 and the data to count starts in cell A2 and goes down.

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A35)-ROW(A2),,1)),IF(A2:A35<>"",MATCH(A2:A35,A2:A35,0))),ROW(A2:A35)-ROW(A2)+1),1))

It's an Array formula and you need to Enter it using Ctrl+Shift+Enter

(found this somewhere on the web and adapted it a bit)

Discuss

Discussion

Thanks Don.
Took me a little while to wrap my head around it and addapt to my use.
Thanks
Dragonhead (rep: 2) Feb 1, '17 at 9:02 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login