Assume a single row of data with numbers and blanks appearing at random intervals.  The objective is to sum the largest 5 of last 10 numbers in that row.  Solving this problem entails multiple steps:

1. Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers
2. Identify the largest 5 of those 10 numbers
3. Sum those largest 5 numbers

Here are the steps

1. Suppose the numbers and blanks are in range A2:V2
2. Type 10 in cell X1
3. Enter this array formula (Ctrl+Shift+Enter) in cell X2

=SUM(SMALL(IF((SUBTOTAL(2,OFFSET(V2,,,1,(COLUMN(\$A2:\$V2)-COLUMN(W2))))<=X\$1)*(\$A2:\$V2)=0,FALSE,(SUBTOTAL(2,OFFSET(V2,,,1,(COLUMN(\$A2:\$V2)-COLUMN(W2))))<=X\$1)*(\$A2:\$V2)),{1,2,3,4,5}))

• Vishwa says:

Nice tip. But I think this should also work.

=SUM(LARGE(OFFSET(Q4,0,-(10+SUM(ISBLANK(\$A\$4:\$Q\$4)*1)-1,1,10+SUM(ISBLANK(\$A\$4:\$Q\$4)*1)),ROW(A1:A5)))

Assuming data is arranged in A4 to Q4

• Hi,

The application does not accept that formula. When I hit the enter key, I get an error dialog box.