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

Thanks dll - very kind of you to say...

Originally Posted by dll

I can't think of anybody who falls into that category.....

I think we both know to whom I was referring

...and without getting into the mutual backslapping too much you would certainly feature in my personal trinity of formula gurus (in no particular order).. can you guess the other two ?

@Thx DK for your classic approach towards formulas and sorry to force you on the solution in one column. Actually one of my friend works in a school and her principal / (owner of the school ) wants to do every work in MS Excel as she thinks she knows about Excel and no one can cheat with her as databases are harmful and someone can easily deceive him. Now my friend time to time visit me and always come with a unique / headache problem. After scolding her , I always try to get some solution and she promised that she will try to consol her principal to adopt the same approach as advised by me but after few days again ..

Anyway, now they gave these options

• If the student pass then his marks & position will appear (Solved)
• If the student fail in any subject then “Fail” will appear. (Solved)
• If any subject's result is awaited they want RL (Result Later on) in the status but if the candidates passed in all others subjects. (Now I solved”)

To solved the problem 3rd I just used your approach by formatting *General;"RL";"FAIL"* then make slightly change in

G2= IF(MIN(B2:F2)<60,0,IF(COUNTIF(B2:F2,"RL")>=1,-1,SUM(B2:F2)))

And

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

I hope above will be acceptable by them. Now I just asking that Is my approach is right or is there any improvement required?

Moreover, I placed the same query on other forum and got a dynamic solution by pgc01 but with lot of advise (almost scolding) to use DK method (now I’ll transfer the same to my friend). You can see exclusive formula by Guru …..



http://www.mrexcel.com/forum/showthread.php?t=400660

Yes I saw PGC01's solution at MrExcel which was as usual of the very highest quality but he too stressed the point that doing in one cell is not really "optimal".
(PGC01 and I are members of both forums - though he operates here under an alternate user name which I won't divulge in case he wishes to remain anom.)

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:

G2= IF(MIN(B2:F2)<60,0,IF(COUNTIF(B2:F2,"RL")>=1,-1,SUM(B2:F2)))

and

H2=IF($G2>0,$G2&"-"&1+SUMPRODUCT(--($G$2:$G$7>$G2),1/COUNTIF($G$2:$G$7,$G$2:$G$7)),IF($G2=0,"FAIL","RL"))
would require swapping RL & FAIL around in the IF

the above changes would have a negligible (to the point of being immeasurable) impact on performance but just highlighting possible opportunities to shorten formulae.

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)))

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 ........