Heloo everybody. The following is my data
No English Maths Science Social
1A1 64 28 40 32
1A10 38 10 27 16
1A11 98 88 100 90
1A12 48 30 36 30
1A13 56 60 26 36
1A14 60 44 51 68
1A15 36 10 12 22
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 58 60
1A21 42 24 28 20
1A22 a a a a
G1= 90
=SUMPRODUCT(--(B2:B16$G$1)) - giving 1 (actual - 0)
=COUNTIF(B2:B16,""&$G$1) - resulting 0
When i remove the "a" in the columns sumproduct is giving the correct
answer. How to get correct answer from Sumproduct. Any suggestions, pls
With regards
Sridhar

If you including "a" the result should be 2 not 1 or 0, look a the third row
down you see a number 98 is greater than 90 and also any letter is greater
than 90

The formula below will ignore any letter
=SUMPRODUCT(--(ISNUMBER(B2:B16)),--(B2:B16G1))


"yshridhar" wrote:

Heloo everybody. The following is my data
No English Maths Science Social
1A1 64 28 40 32
1A10 38 10 27 16
1A11 98 88 100 90
1A12 48 30 36 30
1A13 56 60 26 36
1A14 60 44 51 68
1A15 36 10 12 22
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 58 60
1A21 42 24 28 20
1A22 a a a a
G1= 90
=SUMPRODUCT(--(B2:B16$G$1)) - giving 1 (actual - 0)
=COUNTIF(B2:B16,""&$G$1) - resulting 0
When i remove the "a" in the columns sumproduct is giving the correct
answer. How to get correct answer from Sumproduct. Any suggestions, pls
With regards
Sridhar

Thank you Mama for your suggestion. It works. I need clarification for
these formulae also.
f2==SUMPRODUCT(--(B2:B16G1)*--(C2:C16G1)*--(D2:D16G1)*--(E2:E16G1))

=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))),"")
extracts the IDno, that satisfies the criteria. Any suggestion.
Thank you Mama
With regards
Sridhar

"Teethless mama" wrote:

If you including "a" the result should be 2 not 1 or 0, look a the third row
down you see a number 98 is greater than 90 and also any letter is greater
than 90

The formula below will ignore any letter
=SUMPRODUCT(--(ISNUMBER(B2:B16)),--(B2:B16G1))


"yshridhar" wrote:

Heloo everybody. The following is my data
No English Maths Science Social
1A1 64 28 40 32
1A10 38 10 27 16
1A11 98 88 100 90
1A12 48 30 36 30
1A13 56 60 26 36
1A14 60 44 51 68
1A15 36 10 12 22
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 58 60
1A21 42 24 28 20
1A22 a a a a
G1= 90
=SUMPRODUCT(--(B2:B16$G$1)) - giving 1 (actual - 0)
=COUNTIF(B2:B16,""&$G$1) - resulting 0
When i remove the "a" in the columns sumproduct is giving the correct
answer. How to get correct answer from Sumproduct. Any suggestions, pls
With regards
Sridhar

Don't use "*--" on the first formula, you can either use "*" or ",--" of
course no quotes

ctrl+shift+enter (CSE) on your second formula


"yshridhar" wrote:

Thank you Mama for your suggestion. It works. I need clarification for
these formulae also.
f2==SUMPRODUCT(--(B2:B16G1)*--(C2:C16G1)*--(D2:D16G1)*--(E2:E16G1))

=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))),"")
extracts the IDno, that satisfies the criteria. Any suggestion.
Thank you Mama
With regards
Sridhar

"Teethless mama" wrote:

If you including "a" the result should be 2 not 1 or 0, look a the third row
down you see a number 98 is greater than 90 and also any letter is greater
than 90

The formula below will ignore any letter
=SUMPRODUCT(--(ISNUMBER(B2:B16)),--(B2:B16G1))


"yshridhar" wrote:

Heloo everybody. The following is my data
No English Maths Science Social
1A1 64 28 40 32
1A10 38 10 27 16
1A11 98 88 100 90
1A12 48 30 36 30
1A13 56 60 26 36
1A14 60 44 51 68
1A15 36 10 12 22
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 58 60
1A21 42 24 28 20
1A22 a a a a
G1= 90
=SUMPRODUCT(--(B2:B16$G$1)) - giving 1 (actual - 0)
=COUNTIF(B2:B16,""&$G$1) - resulting 0
When i remove the "a" in the columns sumproduct is giving the correct
answer. How to get correct answer from Sumproduct. Any suggestions, pls
With regards
Sridhar

Thank you Mama. The first one works. But the second array formula is
extracting the IDno. having all "a". How to avoid it?
With regards
Sridhr

"Teethless mama" wrote:

Don't use "*--" on the first formula, you can either use "*" or ",--" of
course no quotes

ctrl+shift+enter (CSE) on your second formula


"yshridhar" wrote:

Thank you Mama for your suggestion. It works. I need clarification for
these formulae also.
f2==SUMPRODUCT(--(B2:B16G1)*--(C2:C16G1)*--(D2:D16G1)*--(E2:E16G1))

=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))),"")
extracts the IDno, that satisfies the criteria. Any suggestion.
Thank you Mama
With regards
Sridhar

"Teethless mama" wrote:

If you including "a" the result should be 2 not 1 or 0, look a the third row
down you see a number 98 is greater than 90 and also any letter is greater
than 90

The formula below will ignore any letter
=SUMPRODUCT(--(ISNUMBER(B2:B16)),--(B2:B16G1))


"yshridhar" wrote:

Heloo everybody. The following is my data
No English Maths Science Social
1A1 64 28 40 32
1A10 38 10 27 16
1A11 98 88 100 90
1A12 48 30 36 30
1A13 56 60 26 36
1A14 60 44 51 68
1A15 36 10 12 22
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 58 60
1A21 42 24 28 20
1A22 a a a a
G1= 90
=SUMPRODUCT(--(B2:B16$G$1)) - giving 1 (actual - 0)
=COUNTIF(B2:B16,""&$G$1) - resulting 0
When i remove the "a" in the columns sumproduct is giving the correct
answer. How to get correct answer from Sumproduct. Any suggestions, pls
With regards
Sridhar