How to sum a row until a condition is met

I have a dataset in which the rows are a student's performance on a test, and the columns are the individual test questions. I would like to create a formula that sums the columns for a student (the test performance; 0 = incorrect, 1 = correct) until there are four 0s in a row. For instance, if a student scored:

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20
1 1 1 1 1 1 0 1 1 0 0 0 1 0 0 0 0 1 1 0

The formula should return 9, because it sums until Q17 where the condition is met (four straight incorrect responses).

Presume performance on the first question for the first student is cell B2 (first row is headers, first column is student name).

Any help would be much appreciated, because this exceed my excel knowledge.

2 Answers

Use this formula that finds the first string of 4 0 and sets that as the end of the range:

=SUM(B2:INDEX(B2:U2,MATCH(1,INDEX((B2:R2=0)*(C2:S2=0)*(D2:T2=0)*(E2:U2=0),),0)))

Consider the following User Defined Function:

Public Function SpecialSum(rng As Range) As Long Dim r As Range, crit As Long, v As Long SpecialSum = 0 crit = 0 For Each r In rng v = r.Value SpecialSum = SpecialSum + v If v = 0 Then crit = crit + 1 Else crit = 0 End If If crit = 4 Then Exit Function Next r
End Function

It adds the values in the range until it encounters four consecutive zeros:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=SpecialSum(B2:Z2)

To learn more about macros in general, see:

and

(v=office.14).aspx

and for specifics on UDFs, see:

Macros must be enabled for this to work!