Applying a countif formula only to visible cells in a filtered list in Excel

The title pretty much sums the query up. I have a table of data with formulas (eg COUNTIF ) applied to each column.

When I filter the column, by date range or region, say, I would like the formula to return the result just for the visible (filtered) entries.

This is the same concept as the SUBTOTAL formula, which provides this functionality but only on a limited number of functions (SUM, AVG,MAX etc).
Any help solving this would be much appreciated.


This is a well know formula on the various forums. To count the "B"s in the range A2 to A100.


 Other excel answers



You can find similar Excel Questions and Answer hereunder

1) How can I set up a drop down list?

2) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

3) How do I enter a formula in a cell using VBA (using Relative Reference)?

4) How can I quickly remove all blank cells in a data range?

5) I am planning to protect my sheet using a password. However, for select cells I would like users to be able to edit the cells. In Excel, how can I achieve that?

6) Formula converts date to quarter and year in Excel

7) I have read that Merging cells should be avoided if possible. In Excel, how can I then make my Title appear in the center of my Data Table?

8) I have a cell with nested formulas that returns an error. In Excel, how can I step through the formulas to help with debugging?

9) How do i apply a formula to an entire column in Excel

10) How can I clear all formats (formats alone not data) from a range of cells?


Here the previous and next chapter