Conditionally extend range reference in formula

Re: Array Function Help: Rank within a conditional range

=SUM(IF($K$25:$K$30="Yes",$L$25:$L$L30))

Something like this? this is an array

Re: Conditionally extend range reference in formula

I have found a way to achieve what you want with a formula, but its a pain to enter and not really practical if you have a large range. As a sample:

=RANK($A2,(IF($B$2="Yes",$A$2,$A$7),IF($B$3="Yes",$A$3,$A$7),IF($B$4="Yes",$A$4,$A$7),IF($B$5="Yes",$A$5,$A$7),IF($B$6="Yes",$A$6,$A$7)),1)

For column of numbers in A2:A6 and Yes/No conditions in B2:B6. In addition you need an additional value in A7. If using a RANK order of 0 then it needs to be =MIN(A2:A6)-1. For RANK order of 1 then needs to be =MAX(A2:A6)+1. Although I did find that setting A7 to 9.99E308 (a VERY large number) worked for both order types, I think this is because of an overflow condition within the RANK function so probably best avoided.

This method relies on submitting the reference to RANK as a series of potentially non-contiguous areas, for which you have to separate the ranges with <comma> and enclose the entire argument with () so that Excel does not misinterpret the <comma> as end of the argument. If there was a way to create the series without the sequence of IF functions then clearly it could be used. So far I have not found a way to do that.

A more practical approach might be to use Advanced Filter and then RANK on the filtered range.