Lookup / countif functions

[davidbrowne17]

ok, here we go.

i have a list of random names in colum A5:A50
and in collum B5:B50 scores

i need a formular that will, for a given NAME , will return the NUMBE
of times that a score of over 100 is achieved. in the tabl
G5:H5.....i'm guessing some kind of conditional LOOKUP & CONTI
functions are needed?

As attached sample sheet.

Any answers apreciated

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54337

 

[rank kabel]

Hi
try
=SUMPRODUCT(--(A5:A50="yourname"),--(B5:B50>100))

 

[davidbrowne17]

thanks rank, but what does the -- signify in the formular?

(e-mail address removed)

 

[Frank Kabel]

Hi
this coerces the boolean values to numbers (TRUE=1;FALSE=0)

 

[JE McGimpsey]

The first of the two - signs in the formula converts the boolean
TRUE/FALSE to -1/0. The second unary minus sign converts -1/0 to 1/0.

This is necessary because SUMPRODUCT() requires numeric arrays, not
boolean.