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

easybpw

Active Member
Joined
Sep 30, 2003
Messages
409
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,927
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
 

Forum statistics

Threads
1,141,619
Messages
5,707,449
Members
421,509
Latest member
kt_mr_excel

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