|There is no built in Excel function that can "look upward", find 4 non zero values and SUM them.|
A User Defined Function (UDF) written in VBA is required.
I wrote this and it seems to work.
1 - Use Alt-F11 to open the VBA editor
2 - Click Insert, Choose Module
3 - Paste the following code into the pane that opens
4 - To use the UDF in a cell, enter =SumFour(cell reference) where cell reference is the cell where you want to start looking up from.
e.g. Based on your example, =SumFour(A10) will SUM A4, A7, A8, A10
5 - You must save the workbook as .xlsm in order for the UDF to be saved.
Note: It's not possible for me to test for every situation or every different type of user input. All I can suggest is that you try the Function and if it returns unexpected results, please explain in detail what you did that caused those results.
Function SumFour(ByVal myRng As Range)
'Loop through rows in descending order
For myRow = myRng.Row To 1 Step -1
'If non-zero found, sum and increment counter
If IsNumeric(Cells(myRow, myRng.Column)) And _
Cells(myRow, myRng.Column) <> 0 Then
tempSum = tempSum + Cells(myRow, myRng.Column)
sumCount = sumCount + 1
'Stop when 4 values Summed
If sumCount = 4 Then GoTo sum4
'If 4 values were not found, present Message
If sumCount < 4 Then
SumFour = "4 Values Not Available"
'If 4 values were found, present Sum
SumFour = tempSum
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.