# Count If Visible Fields Advice

#### VBA learner ITG

##### Board Regular
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?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### VBA learner ITG

##### Board Regular
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

##### MrExcel MVP
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

##### MrExcel MVP
Simpler

Denominator
=SUBTOTAL(3,B6:B6345)

M.