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

easybpw

Active Member
Joined
Sep 30, 2003
Messages
411
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,463
Office Version
  1. 2016
Platform
  1. Windows
Hi EasyBPW,

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

EasyBPW.xlsx
ABC
1
2Result=9
3
4CardTypeValue
5VisaGas22
6AmexFood33
7MetroDrink44
8VisaClothing55
9AmexGas66
10MetroFood77
11VisaDrink88
12AmexClothing99
13MetroFood111
14
Sheet1
Cell Formulas
RangeFormula
C2C2=AGGREGATE(3,7,$A$5:$A$999)


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

1626112901527.png
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,483
Messages
5,770,361
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top