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