MODE function excluding hidden rows

michael_1962 · #1 05-17-2015, 11:03 AM

How do I make this Mode.sngl function to work only for visible rows?

=MODE.SNGL(IF($I$2:$I$5701=P2, IF($J$2:$J$5701=Q2,$L$2:$L$5701)))

I will be grateful for your help

Thanks

excelledsoftware · #2 05-21-2015, 10:09 PM

Besides autofilters or VBA I am not aware of a way to have a formula determine if a row is hidden. With an autofilter you can fill the formula in and it will ignore the hidden ones but VBA may be a better bet for you.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA to keep hidden rows hidden when filtering	tectonicseer	Excel Programming	1	07-27-2014 11:54 AM
a function that deletes all rows with no value in a certain column?	dunndealpr	Excel	2	06-08-2013 06:50 AM
how to view hidden rows	gsrikanth	Excel	10	01-27-2012 05:29 AM
hidden rows	gsrikanth	Excel	5	01-19-2012 02:40 PM
Word won't come back to normal mode from proofreading mode	prg4hire	Word	2	09-25-2010 09:46 AM