Combining multiple OR conditions with AND in Excel formula

[sjedi]

I am trying to combine OR conditions with AND over multiple columns in my Excel datasheet and would like advice on how to do this. A sample table is shown below.

The formula does not calculate the two OR conditions for [Year] and [Season].

=MEDIAN(IF((Table1[Fruit]="Apple")*OR(Table1[Year]=2023,Table1[Year]=2024)*OR(Table1[Season]="Summer",Table1[Season]="Spring"),Table1[Value]))

The above formula gives Median = 5. The correct answer is Median = 5.5

Season	Year	Fruit	Value
Summer	2023	Apple	3
Winter	2023	Apple	5
Spring	2023	Banana	10
Summer	2023	Banana	15
Winter	2024	Apple	50
Spring	2024	Banana	2
Summer	2024	Apple	8
Winter	2025	Banana	12
Spring	2023	Orange	42
Summer	2023	Orange	20
Winter	2024	Orange	9
Spring	2025	Apple	2
Spring	2025	Banana	10

Correct filtering of [Fruit] = Apple, [Year] = 2023 or 2024, [Season] = Summer or Spring:

Median (3,8) = 5.5

Season	Year	Fruit	Value
Summer	2023	Apple	3
Summer	2024	Apple	8

 

[Cubist]

Must use "+" for OR as you've used "*" for AND.

=MEDIAN(IF((Table1[Fruit]="Apple")*((Table1[Year]=2023)+(Table1[Year]=2024))*((Table1[Season]="Summer")+(Table1[Season]="Spring")),Table1[Value]))