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:

G2:
=IF(COUNTIF($B2:$F2,"<60"),"Fail",SUM($B2:$F2)&"-"&1+SUMPRODUCT(--(SUM($B2:$F2)<(SUBTOTAL(9,OFFSET($B$2:$F$2,ROW($B$2:$F$7)-ROW($B$2:$F$2),0))*(SUBTOTAL(5,OFFSET($B$2:$F$2,ROW($B$2:$F$7)-ROW($B$2:$F$2),0))>=60)))))

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.

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,