Median with various criteria-CodePudding

here's my problem: I need to calculate the median from the following table:

V1  V2  Total
A   0   0
B   2   10
C   2   12
D   2   19
E   2   22
A   2   4
B   1   12
D   1   0
C   2   8
A   0   10
D   1   15
A   2   12
B   2   10
E   1   16

Criteria are as follows:

'B', 'C', and 'D' from column V1
not zero from column Total
calculate median from column Total

Until now, the formula works perfectly:

=MEDIAN(IF(B2:B15={"B","C","D"},IF(NOT(D2:D15="0"),D2:D15)))

And now comes the hard part. It has to include another criteria, which is:

'A' from column V1 only if not 0 in column V2

I have no idea how to embed it in the code above, because it gives me various types of errors, depending on what I try.

CodePudding user response：

An idea using Microsoft365:

Formula in E2:

=MEDIAN(FILTER(C2:C15,(ISNUMBER(FIND(A2:A15,"BCD")))*(C2:C15<>0) (A2:A15="A")*(B2:B15<>0)))