Formula debugging

[yshridhar]

I am sorry to repost this query. The following is my data
No English Maths Science Social
1A1 64 28 40 32
1a1 38 10 27 16
1A11 98 80 80 60
1A12 48 30 36 30
1A13 56 60 26 36
1A14 60 44 51 68
1A15 a a a a
1A16 36 6 7 6
1A17 56 44 52 48
1A18 48 26 18 36
1A19 50 42 41 42
1A2 46 38 31 24
1A20 80 66 70 61
1A21 a a 28 20
1A22 36 10 12 22

=SUMPRODUCT(--(B2:B16>=G1)*--(C2:C16>=G1)*--(D216>=G1)*--(E2:E16>=G1))
It calculates the no. of pupil getting more than a mark in G1 in all the
subjects. However this formula also includes the IDno. with all "a"s.

=IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(($B$2:$B$16>=$G$1)*($C$2:$C$16>=$G$1)*($D$2:$D$16>=$G$1)*($E$2:$E$16>=$G$1),ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"")
This array formula returns the IDno.s of the pupil who secured more than a
mark in G1 in all the subjects. But here also same problem; it is retuning
the IDno with all "a"s.
In case of "<" i am not getting this sort of problem. Only in case of ">"
the formula fails.
Any suggestions, pls.
With regards
Sridhar

 

[T. Valko]

Try these:

For the count:

=SUMPRODUCT(--(MMULT((ISNUMBER(B2:E16))*(B2:E16>=G1),{1;1;1;1})=4))

To extract those that meet the criteria (array entered):

=IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(MMULT((ISNUMBER(B$2:E$16))*(B$2:E$16>=G$1),{1;1;1;1})=4,ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"")

There might be a better way to write that formula but I don't have time to
play with it right now.

 

[yshridhar]

Thanks alot Mr. Biff. The formulae modification solved the problem. Hoping
that you may get some time from your valuable seconds to refine the formula.
With regards
Sridhar

 

[T. Valko]

I think that's about it unless you want to use a column of helper formulas.