Forum Discussion
Formula to Count only filtered columns but also doesnt count hidden cell formulas
All,
I have a subtotal formula (=subtotal(3,xx:xx)) counting cells and it does a excellent job of count only filtered cells (adjusting to the filter)
However if i throw in a formula to the cells it will count all the formulas. Even though they might evalute to blank
Is there a better formula out there I need to be using?
You can use the “SUBTOTAL” function with the function number 109 to count only visible cells that contain any value, including text and logical values. The difference between using number 103 and 10` is that 103 counts only visible cells that contain numbers, while 109 counts all visible cells that contain any value. You can choice between 103 and 109.
For example, if you have a range of cells that contains numbers, text, and logical values, and you want to count only the visible cells that contain any value, you can use the formula =SUBTOTAL(109,A2:A10) .
I hope this clarifies the difference between using 103 and 109 with the SUBTOTAL function.
I hope this helps!
Subtotal works, but 109 is not the correct number for counting, it's for 'SUM'. 103 will perform 'COUNTA' on only visible cells.
My earlier response needs clarification regarding the function numbers used in the SUBTOTAL formula. For counting non-empty visible cells (including formulas that might evaluate to blank), use SUBTOTAL(103, ...), where 103 performs the COUNTA operation, ignoring hidden rows but still counting cells with formulas, even if they appear blank.
To exclude hidden rows AND cells with formulas that evaluate to blank, ,maybe you can use the following workaround:
=SUMPRODUCT((SUBTOTAL(103, OFFSET(A2:A100, ROW(A2:A100)-ROW(A2), 0)) = 1) * (A2:A100 <> ""))
- You could use AGGREGATE to count the cells. It's capable of ignoring cells with SUBTOTAL formulas.
