Count If Visible Fields Advice

VBA learner ITG

Board Regular
Joined
Apr 18, 2017
Messages
167
Office Version
365
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?
 

Some videos you may like

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
Joined
Apr 18, 2017
Messages
167
Office Version
365
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
Joined
Aug 23, 2010
Messages
16,371
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,344
Messages
5,486,303
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top