Finding the Median with Conditions and Excluding Hidden Rows

Hi, everyone. I need a function to find the median with conditions, and excluding hidden rows. For example, let's say we need to find the median of column B, but only for rows where: column A is either "apple" or "pear", column C is populated, and the row is not hidden/filtered.

I haven't found a way to use =AGGREGATE with extra conditions, and I don't know of a way to use =MEDIAN while excluding hidden rows (though I do know how to use =MEDIAN with other conditions). I'm not very experienced with VBA and would prefer not to use it if I don't have to. Does anybody know how to do this? Let me know if any more information would be helpful.


Edit: Here's a sample worksheet.
test.xlsx

A sample sheet would have helped... but try:

=LET(A,A2:A20,B,B2:B20,C,G24,D, OFFSET(B,ROW(B)-MIN(ROW(B)),0,1,1),SUMPRODUCT(--(A=G24),SUBTOTAL(109,D))/SUMPRODUCT(--(A=G24),SUBTOTAL(103,D)))

I am now confused by your criteria.

1. Please highlight those rows which you wish to have included in the calculation.

2. Confirm that it is the MEDIAN (as opposed to the average or the mean) that you want.

The answer is required to be 9, but it should be actually 7

Unless you change this MEDIUM FUNCTION to MAX

You simply test the following formula =MEDIAN({9;9;3;2;7;7})

Array formula

Formula:

Please Login or Register  to view this content.

I agree with wk9128. The answer IS 7, but it's not an array formula in your Excel version. Just Enter.