Medianifs - please help translate an averageifs formula

[mahashakti]

The following is a well functioning averageifs formula. I need a formula that will look at the same criteria and return the median for the given ranges. Please help translate this. I've been googling and don't see this complicated of a question anywhere. (Thank you in advance!)

=IFERROR(AVERAGEIFS('Transaction Detail'!$L:$L,'Transaction Detail'!$A:$A,$A18,'Transaction Detail'!$J:$J,">"&N$17,'Transaction Detail'!$J:$J,"<"&N$18),"N/A")

 

[DRSteele]

Does this help? watch here: https://www.youtube.com/watch?v=OSBEPZ4L10I

 

[mahashakti]

I got help solving this on Excel Help - A Reddit Community

 

[mahashakti]

Thank you, I will watch this later! My question was solved with and array formula. Push Ctrl+Shift+Enter to make it happen!
=MEDIAN(IF('Transaction Detail'!A:A=A18,IF('Transaction Detail'!J:J>0.0001,IF('Transaction Detail'!J:J<=10000000,'Transaction Detail'!L:L,"N/A"))))

 

[joeu2004]

The following is a well functioning averageifs formula. I need a formula that will look at the same criteria and return the median for the given ranges.

=IFERROR(AVERAGEIFS('Transaction Detail'!$L:$L,'Transaction Detail'!$A:$A,$A18,
'Transaction Detail'!$J:$J,">"&N$17,'Transaction Detail'!$J:$J,"<"&N$18),"N/A")

Array-enter the following formula (press ctrl+shift+Enter instead of just Enter):

=IFERROR(MEDIAN(IF('Transaction Detail'!$A$1:$A$10000=$A18,
IF('Transaction Detail'!$J$1:$J$10000>N$17,
IF('Transaction Detail'!$J$1:$J$10000 lt N$18,'Transaction Detail'!$L$1:$L$10000)))),"N/A")

Note: Replace "lt" with "<". This forum's interface has trouble when I type the symbol in that context. (Sigh.)

Note that I write $A$1:$A$10000 instead of $A:$A. It is always risky to write $A:$A because it might cause Excel 2007 and later to process 1+ million rows. But it is even more important in array-entered formulas because it does cause Excel 2007 and later to create one or more arrays of 1+ million rows.

You probably never will have 1+ million rows of data. But if you might have more then 10,000 rows, choose a larger number. Just be sure to be consistent in all ranges in the array-entered formula.