Count If Visible Fields Advice

[VBA learner ITG]

Hi all,

I have this formula which provides a percentage of column B count for a Yes or No answer.

=COUNTIF(B6:B6345,"YES")/COUNTA(B6:B6345)
=COUNTIF(B6:B6345,"NO")/COUNTA(B6:B6345)

I need to know if there is another formula I could amend the above to so it counts visble fields only after i have applied a filter on the column?

 

[Marcelo Branco]

Count visible cells
https://exceljet.net/formula/count-visible-rows-in-a-filtered-list

Count visible cells with criteria
https://exceljet.net/formula/count-visible-rows-only-with-criteria

M.

 

[VBA learner ITG]

Thank you for reviewing, I have decided to go with the below.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B6345,ROW(B6:B6345)-ROW(B6),0,1))*(B6:B6345="yes"))/COUNTA(B6:B6345)
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B6345,ROW(B6:B6345)-ROW(B8),0,1))*(B6:B6345="no"))/COUNTA(B6:B6345)

 

[Marcelo Branco]

I think you should use a similar formula in the denominator, but excluding the condition ("Yes"), i.e.:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B6345,ROW(B6:B6345)-ROW(B6),0,1)))

Otherwise, you will be counting all the rows, not just the visible ones.

M.

 

[Marcelo Branco]

Simpler

Denominator
=SUBTOTAL(3,B6:B6345)

M.