Count formula to count only cells that contain specific text not filtered

[easybpw]

Hello and thank you in advance for your help.

I have a spreadsheet that contains many rows and columns. In certain cells contains specific text; AMEX, VISA, MC, Loan, Donation, etc. There also is an auto filter on so when data changes certain rows are filtered out. I want to be able to count every occurrence of these specific terms but not if they are filtered out. For example my date range may be a2:z100 and in that range "AMEX" may occur 50 times. But if a specific filter is in place then AMEX is only visible 20 times. Every formula I try includes the filtered cells and I am continuing to show 100 as my result when I only want it to be 20. How can I do this?

Thanks!

 

[Toadstool]

Hi EasyBPW,

You could use the COUNTA function of AGGREGATE with the option to ignore hidden cells.

EasyBPW.xlsx
	A	B	C
1
2		Result=	9
3
4	Card	Type	Value
5	Visa	Gas	22
6	Amex	Food	33
7	Metro	Drink	44
8	Visa	Clothing	55
9	Amex	Gas	66
10	Metro	Food	77
11	Visa	Drink	88
12	Amex	Clothing	99
13	Metro	Food	111
14
Sheet1

Cell Formulas
Range	Formula
C2	C2	=AGGREGATE(3,7,$A$5:$A$999)

If I filter only for Food and Drink the count goes from 9 to 5.