Search code examples
excelexcel-formula

Create a list with SORTING and FILTER function with multiple criteria in a range

A B C D E F G H I J K L M N O P
1 2024 2024 2025 2025 RowCrit1 RowCrit2 ColCrit1 ColCrit2
2 HY1 HY2 HY1 HY2 2025 HY1 MUC A321
3 FRA B717
4 Flight A MUC 500 70 60 80 A321
5 Flight B MAD 100 47 300 100 A380
6 Flight C FRA 800 21 200 360 B717 Results
7 Flight D MAD 90 56 150 578 A321 Flight C
8 Flight E PAR 45 700 790 800 A220 Flight A
9 Flight F FRA 600 150 40 10 B717 Flight F
10 Flight G MAD 900 90 980 453 B757

In Cell O7 I want to display a descending list of the data in Column A based on the numbers in Range E4:H10 and the multiple criteria in L2, M2 and O2:O4 and P2:P4.

So far I have been able to develop this formula:

=SORT(LET(
    a, COUNTIF(O2:O4, C4:C10) + AND(O2:O4 = ""),
    b, COUNTIF(P2:P4, J4:J10) + AND(P2:P4 = ""),
    FILTER(FILTER(E4:H10, (E1:H1 = L2) * (E2:H2 = M2), ""), a * b, "")
),,-1)

It gives me the following result:

A .. O P
1
:
:
7 Results
8 200
9 60
10 41

This results is already correct from the numbers and the sorting.

However, how do I have to modify the formula so it displays the flights from Column A and not the numbers?

Solution

  • enter image description here

    Formula in O7:

    =TAKE(SORT(FILTER(HSTACK(A4:A10,FILTER(E4:H10,(E1:H1=L2)*(E2:H2=M2))),COUNTIF(O2:O3,C4:C10)*COUNTIF(P2:P3,J4:J10)),2,-1),,1)