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:
- Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers
- Identify the largest 5 of those 10 numbers
- Sum those largest 5 numbers
Here are the steps
- Suppose the numbers and blanks are in range A2:V2
- Type 10 in cell X1
- 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}))
Sum the largest 5 of the last 10 numbers in a row ignoring blanks
{ 2 Comments }