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 ce...
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 <> ""))