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.
Answer:
This is a well know formula on the various forums. To count the "B"s in the
range A2 to A100.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="B")+0)
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?