Using multipel sum ranges in Sumproduct() & countif() in array

Originally Posted by DonkeyOte

Re: your revisions - all look fine to me though I would reiterate the point we've discussed previously that only 0 equates to FALSE in XL so you could remove some of your tests as they're not truly required... in the below formulae I've highlighted in red those parts that could be removed:


You could also think about removing the IFs and using a CHOOSE approach based on SIGN of G2

H2=CHOOSE(SIGN($G2)+2,"RL","FAIL",$G2&"-"&1+SUMPRODUCT(--($G$2:$G$7>$G2),1/COUNTIF($G$2:$G$7,$G$2:$G$7)))

Oh its great DK, I never used SIGN but you have shown a new way to use sign with Choose(). In future I'll try to adopt choose.
Thx for your typical explanations ........