Need help with counting the total number of entries that change when filter is used

[crazy_papaya]

Hi all,

How can we count the total number of rows/ products in "Product name" and also for the count to change every time a filter is used.

So for ex - In table below If I filter out P1 then I want total to show "3" and if filter nothing, total becomes back to "4"

I have found out how to count number in a dynamic way using =SUBTOTAL (9,RANGE) I just need to know how to count names in a dynamic way. Any insights?

Product name	qty
P1	1
P2	1
P3	1
P4	1

 

[Toadstool]

Hi Crazy_papaya,

Does this work?

Book1
	A	B
1	Product name	3
3	P2
4	P3
5	P4
Sheet1

Cell Formulas
Range	Formula
B1	B1	=AGGREGATE(3,5,A:A)-1

 

[crazy_papaya]

Yay! It worked !

Thank you Toadstool