Median/If Array Working - Almost...

Because, there are enough blanks in D where A:A=F2. Those are evaluated to 0. So median value = 0.

You should also add another condition where D:D<>"".

As well, don't use entire column reference in IF logical calculation. Unlike SUMIF/COUNTIF(S) family of functions, it does not shrink down calculation range to used range and creates unnecessary overhead.

Instead use dynamic named range.

Ex:

Formula:

Please Login or Register  to view this content.

Where
lRegion: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
lTurnover: =Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNTA(Sheet1!$A:$A))

My debugging steps:

1) Use Evaluate formula tool to see what is happening in I2 (https://support.office.com/en-us/art...6-a70aa409b8a7 ). I notice that each of the "blanks" is turned into a 0 by the IF() function. MEDIAN() does not ignore the value 0 (it will ignore blanks), so the median of a group of numbers where most entries are 0 will be 0. The error is due to the IF() function making the blank entries 0.
2) Which made me wonder if the other columns are working correctly (I don't think they are). If I use Advanced filter to fully remove the MEE group from the data set, and get the Median of each column, I get 29.5 for the median of column B, 169 for the median of column C, and 33 for the median of column D. I think all of your medians are wrong -- and they will be wrong because the IF() function inside of the MEDIAN() is changing blanks to 0 (standard spreadsheet behavior).

Solution is to do something different with your data or your IF() function. Maybe ...IF($A:$A=$F2,IF(ISBLANK(B:B),"",B:B),"")... so that it tests for both conditions (region matches text and if entry is blank).