Hi Biff!

In article ,
"Biff" wrote:

Change the values in Sheet1 row 19 to:

85:91

Sheet2 row 19 = ""

Thanks for catching it! Much appreciated!

This is a real challenge!

Most definitely! And I do like a challenge! :)

Here's my second attempt at a solution. This time I hope I've covered
all the bases. I guess we'll see... :)

Sheet2!B19, copied across and down:

=IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/((Sheet1!$C19:$H19-Sheet1!$B19:$G19=
1)*(Sheet1!$B19:$G19=80)*(Sheet1!$C19:$H19<=89))) )=B$1,SUM(B$1,A19),IF(C
OUNTIF($A19:A19,"0")=1,SUM(A19,1),""))

....confirmed with CONTROL+SHIFT+ENTER. Note that the same caution
applies here. Column A, starting at A19, must either be blank or
contain text values.

Hope this helps!