Calculating median using dates

I am trying to calculate the median, using a set of dates as a criteria.

I just cannot get it to work

In the example screenshot, I have attempted to get it to calculate the median for January. The result should be 5.

When I attempt to calculate for month = December, it calculates the median using all 4 values (including January)

You need to force the formula into array-mode context by closing it with Shift+Ctrl+Enter instead of just Enter.

Otherwise for functions and operators that take a scalar value (like MONTH()) it takes the implicit intersection of the formula position and the range reference; as the formula is on row 3 here that is A3. That’s what you observe for December because A3 contains a December date, MEDIAN() then calculates 4 values because IF() returns the entire B3:B6 range. If you entered your formula in non-array-mode in let’s say N7 that does not intersect rows 3:6 then the result would be a #VALUE! error.

In array-mode for each matching value in A3:A6 (of the IF(MONTH(A3:A6)=1,…)) the corresponding value in B3:B6 is returned and for non-matching values in A3:A6 a “non-element” as the ELSE path of IF() is empty, and the resulting matrix then is fed to MEDIAN().

Brilliant explanation, thank you.

I now have it working.

@rochdalerules Please upvote and mark the answer as correct by clicking its check mark. Thanks.