##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

# 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?

## 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)

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