Calculate Median with multiple conditions

hi there. you need to change the cells in H5 and I5 to show just the Type like in Column C. just "A" and "B". then perhaps this array formula in cell H6:
=MEDIAN(IF(Tabell1[Fruit]=$G6,IF(Tabell1[Type]=H$5,Tabell1[Value])))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

otherwise:
=MEDIAN(IF(Tabell1[Fruit]=$G6,IF(Tabell1[Type]=SUBSTITUTE(H$5,"Type ",""),Tabell1[Value])))

do also consider to input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

Hello Masun,
You can use Averageifs function.

Formula:

Please Login or Register  to view this content.

Please refer attached excel for more clsrification.

@sandy:
i tried using the PowerQuery too. and i got the same answers as yours and they don't seem right. Orange for Type A if you filter the Table is:
13
34
14
22

in ascending order would be:
13
14
22
34

Median would be the average of 14 and 22; 18. the answer we get from Power Query is 14. not sure how it derived it. but it seems like for banan type B, and Apple Type B, Power Query took the 2nd number if it has 4 numbers in total instead of averaging it.

ps: googled but forgot to read it before i posted. seems like a bug
https://www.myonlinetraininghub.com/...ng-power-query

Because PQ Median treat blank values as 0 (not null)
see attached file and try with/without zero

because OP didn't show what he want to achieve I set simplest way

Edit: I can calculate median for each product separately then join it to one table with null instead of 0

Should be ok now
PQ/PPT

i suggest you don't label the cell like you did in G15:G17. it is possible but difficult for the formula to pick it up. for what you did in G20:G22, add in the 2nd fruit in H20:H22
Orange
Apple
Banana

Ensure I19:J19 has the Type too; A. B.

then in I20:
=MEDIAN(IF((Tabell1[Fruit]=$G20)+(Tabell1[Fruit]=$H20),IF(Tabell1[Type]=I$19,Tabell1[Value])))

ps: it's also good to try the solutions or acknowledge the efforts of other members who chipped in. i think Jose's AGGREGATE formula might work faster. but i hardly give AGGREGATE solutions as they are only available from Excel 2010 onwards. it will fail if you send it to someone else with lower version.
sandy's solution should be interesting too. tedious at the start but easier to maintain later. calculates faster. again, only available from Excel 2010 onwards

Originally Posted by Masun

Hi,

Thank you very much! All of you! It is truly amazing with all the competence and willingness to help!

Good to hear that you got answer for your question.