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

Hi Excel Gurus,

After completing almost 106 days I am very thank full Forums experts as because of them I am able to use some formula more effectively.

Now I've jammed in below problem and almost struggling from last 2 days, At last, I ve got the solution but seeking for some improvement in formula. Hope I'll get some cool solution from you. My problem is :

1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Now in H column I want use this formula which I obtained from this forum
H2=SUMPRODUCT((G$2:G$7>G2)/COUNTIF(G$2:G$7,G$2:G$7&""))+1
To get the position of Students.
But the text value "fail" in the G2:G7 getting Position No. 1 and i've noticed the reason by using evaluate formula as well.
3. I got solution by changing "Fail" with 0 by creating column I and then column H put this formula.
=SUMPRODUCT((I$2:I$7>I2)/COUNTIF(I$2:I$7,I$2:I$7&""))+1
or
=IF(I2=0,"Fail",SUMPRODUCT((I$2:I$7>I2)/COUNTIF(I$2:I$7,I$2:I$7&""))+1)
Now after spending a more time I made this array formula for H2 to control Text Values
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
or
={IF(COUNTIF(B3:F3,">=60")<>5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
And more work to get total marks + position ......
={IF(COUNTIF(B2:F2,">=60")<>5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
Now I am wounder that is there a way to get rid of Column G ?????
By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}

All the things are briefly described in attached sheet.

Maybe I'm missing something but could't you just use the below (and dispense with I & J)

H2: =IF(ISNUMBER($G2),$G2&"-"&1+COUNTIF($G$2:$G$7,">"&$G2),"Fail")
copied down

EDIT: perhaps you're saying you want to generate H without need for G, I or J ?

If I'm correct and you want to dispense with all but A:F and present results in G then perhaps:

Please Login or Register  to view this content.

Note however that this is Volatile Sumproduct and as such performance is not going to be great - you would IMO be best served forgoing elegance for the sake of efficiency and using your existing setup of G and previously provided solution for H.

You could probably do the above with a FREQUENCY Array but these aren't my strong point - I will take a look though.

Oh thx DK for a nice solution in H2 and yes i only wanted to show result in Cell G and agree with your suggestion but i really wanted this just to increase my knowledge and this can be helpful in some circumstances ............

About Frequency i just checked it out, it seems to be useful but telling you honestly first time i tried this function but will try to dig in....
anyway I'll wait for some more effective & fast solution from you .....

anyway I'll wait for some more effective & fast solution from you .....

Does the formula in post #3 not resolve your issue ?
ie it only requires the data in B:F

Originally Posted by DonkeyOte

Does the formula in post #3 not resolve your issue ?
ie it only requires the data in B:F

yes my problem is solved and i was just hoping you to do this by using FREQUENCY

Anyway, I've got the best solution as per expectations and does not force you to do more work on it ......

cheers,

@Sorry DK but i was just rechecking your formula and noticed if two students get equal positions then next position will be skipped like if two students get position no. 1 at the same time then position no. 2 will be skipped and same issue in case two students get the same position no. 2 then 3 will be skipped .......
Any idea ???

That's the basic premise of ranking, ie

20: 3
30: =1
30: =1

The 2nd instance of Rank 1 takes the place of what would otherwise have been Rank 2.

Are you saying you want:

20: 2
30: 1
30: 1

Originally Posted by DonkeyOte

Are you saying you want:

20: 2
30: 1
30: 1

Yes DK, I require above result and don't want rank but positions for a school's result ......
My formula is working fine but for the sake of knowledge & brainstorming I am trying to do all calculations in Column H OR looking for quick & short solution.

I confess I'm not sure the results you want make a great deal of sense to me (given in the example position 2 is rally the 2nd instance of position 1 and thus does not exist per se) but... I would still be inclined to revert back to an older incarnation of your solution so as to keep as efficient as possible...

G2: =IF(MIN(B2:F2)<60,0,SUM(B2:F2))
copied down as required

Apply format to G2:G7 of: General;;"FAIL"

Using the Custom Format approach you can leave the underlying values in G as numerics to simplify latter calcs but have the cell appear to contain text string.

Then to generate your desired result of Score-SpecialRank / Fail use:

H2: =IF($G2,$G2&"-"&1+SUMPRODUCT(--($G$2:$G$7>$G2),1/COUNTIF($G$2:$G$7,$G$2:$G$7)),"Fail")
copied down as required

This way you're avoiding the use of Volatile Sumproducts etc...

I know others with greater skills than me are looking at this so you might still (if you're lucky) get a humdinger of a solution... the above is based purely on my own opinion & skillset.

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 ?

Originally Posted by daddylonglegs

Luke, who do you mean? I can't think of anybody who falls into that category.....

At that point I concluded that the best man for the job was already on the case.....great stuff

Agreed with Dll as from the day i joined here, every time i look exceptional replies from DK ........

Originally Posted by DonkeyOte

I confess I'm not sure the results you want make a great deal of sense to me (given in the example position 2 is rally the 2nd instance of position 1 and thus does not exist per se) but... I would still be inclined to revert back to an older incarnation of your solution so as to keep as efficient as possible...

G2: =IF(MIN(B2:F2)<60,0,SUM(B2:F2))
copied down as required

Apply format to G2:G7 of: General;;"FAIL"

I know others with greater skills than me are looking at this so you might still (if you're lucky) get a humdinger of a solution... the above is based purely on my own opinion & skillset.

@DK this part is cool by the use of custom format ....

As i earlier told that i wanted students positions in the class so thats why i couldn't use rank() function because rank and positions are different .... like in a class 1st position can be achieved by 2 students and 2nd can be for 3 but 2nd position can not be vacant ........

I think now this formula can't be shorten but i was just thinking the possibilities of using only column G but please i am 101% agreed with your suggestion and solution of using column G and then H ....... thx for spending time on my solution .....

cheers,

@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

Please Login or Register  to view this content.

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