Year values in the column

Hello,

Let me explain what my data looks like, so I can ask you, good people, a question. I got 3 columns: Industry, Year and Value:

Industry Year Value
001 2016 8
001 2016 11
001 2015 12
002 2015 7
002 2015 6
002 2015 1
003 2016 4
003 2016 9
003 2016 3

I need to calculate median for each industry. I know how to do that using MEDIAN and IF formulas {=MEDIAN(IF($A$2:$A$7=001,$C$2:$C$7))}

Here is the part that I would like help with for following logic:

If the years within the same industry are all 2016, use all values to calculate a median
If the years within the same industry are combination of 2015 and 2106, use only 2016 values to calculate a median.
If the years within the same industry are all 2015, use all values to calculate a median

I think the way to do this is to create an extra column with some kind of indicator whether or not to include the value in median calculations. I'm just not sure how to do it.

Thank you as always in advance!

Try

=MEDIAN(IF(COUNTIFS($A$2:$A$10,1,$B$2:$B$10,2015)=COUNTIF($A$2:$A$10,1),IF($A$2:$A$10=1,$C$2:$C$10),IF($A$2:$A$10=1,($C$2:$C$10)*($B$2:$B$10=2016))))

Thank you for trying , John! Unfortunately it doesn't work. When all year values are the same (2015 or 2016) it calculates right median, but if it's a mix, it doesn't.

Try

=MEDIAN(IF(COUNTIFS($A$2:$A$10,1,$B$2:$B$10,2015)=COUNTIF($A$2:$A$10,1),IF($A$2:$A$10=1,$C$2:$C$10),IF(($A$2:$A$10=1)*($B$2:$B$10=2016),($C$2:$C$10))))

Enter with Ctrl+Shift+Enter