A library to have access to the LET functionNot sure what you mean.
=IFERROR(INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!C:C="MISSING")*(TEST!$A:$A<=$F$4))))));2;-1);SEQUENCE(5;1;1);{1\2});"-")
Great thanks. Much better.You will need install the latest updates.
Also I think that you can simplify your formulae likeExcel Formula:=IFERROR(INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!C:C="MISSING")*(TEST!$A:$A<=$F$4))))));2;-1);SEQUENCE(5;1;1);{1\2});"-")
Could you give me a hint? I really need to put them at least in two formulas instead of three so that I get the top 5 in the two possible scenariosIt's possible, but I would not recommend it.
Because I have two scenarios, and I need the top 5 for each of them.Why can't you just keep them as three separate formulae?
If you put them all into one formula then they will be recalculating when there is no need.
For instance the formula in post#12 looks at col C, but your other two don't, so with 3 formulae only one will recalc if you change col C rather than one massive formula recalculating.
=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!C:C="MISSING")*(TEST!$A:$A<=$F$4))))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")
=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4))))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")
=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250"))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$D:$D;"<"&EDATE($F$4;1);TEST!$E:$E;"<>R.";TEST!$E:$E;"<>7250";TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250"))))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$D:$D;"<"&EDATE($F$4;1);TEST!$E:$E;"<>R.";TEST!$E:$E;"<>7250";TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250")))));-1);SEQUENCE(5;1;1);{1\2});"-")
I'm now totally confused, what have those two formulae got to do with either of theseExcel Formula:=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!C:C="MISSING")*(TEST!$A:$A<=$F$4))))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")
Excel Formula:=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4))))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")
Excel Formula:=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250"))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$D:$D;"<"&EDATE($F$4;1);TEST!$E:$E;"<>R.";TEST!$E:$E;"<>7250";TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250"))))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$D:$D;"<"&EDATE($F$4;1);TEST!$E:$E;"<>R.";TEST!$E:$E;"<>7250";TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250")))));-1);SEQUENCE(5;1;1);{1\2});"-")