SUM of – Last 12 values in a Column, Largest 5 out of Last 12 values, last 12 values Excluding Highest / Lowest

 

---

 

SUM of Last 12 values in a Column; Sum Largest 5 out of Last 12 values; SUM last 12 values Excluding Highest / Lowest values.

  

Cell F3 formula: Return SUM of last 12 values in column A (A2:A30), ignore blanks and text values.

 

Cell F5 formula: Return Sum of Largest 5 out of Last 12 values in column A (A2:A30), ignore blanks and text values.   

 

———————————————————————————————– 

 

Cell F3 formula: Return SUM of last 12 values in column A (A2:A30), ignore blanks and text values.

 

The calculation is done for column A (A2:A30) which may contain numbers, blank cells or text values.

  

Cell F3 formula: 

  

=SUM(INDEX( A2:A30, LARGE( INDEX( ISNUMBER( A2:A30)* (ROW( A2:A30)- ROW(A2)+1),), 12)):A30)  

  

Explanation – Cell F3 formula (Normal, Non-Array formula): 

 

1) ISNUMBER( A2:A30)* (ROW( A2:A30)- ROW(A2)+1): returns an array of row numbers / positions for cells which have numbers – {1;2;3; 4;0;6; 7;8; 9;10; 11;12; 13;14; 15;0; 17;18;19; 20;0;22; 0;24;0; 0;0; 0;0}. The zeros indicate cells which have text or blank cells, so that position no 5 (ie. cell A6) has text value, position no 16 (ie. cell A17) has text value, position no 21 (ie. cell A22) is blank, and so on.

 

2) LARGE(INDEX( ISNUMBER( A2:A30)* (ROW( A2:A30)- ROW(A2)+1),), 12): returns the 12th Largest row number / position, ignoring cells which are blank or with text values. The formula returns 10, which indicates cell A11, in the array 1) above. Note: INDEX is used here to keep the formula as non-array.

 

3) INDEX(A2:A30, LARGE( INDEX( ISNUMBER( A2:A30)* (ROW( A2:A30)- ROW(A2)+1),), 12)):A30: uses the 12th Largest value of 2) above with INDEX function “A2:A30” which determines the start cell / position of the array and returns an array of  values starting from this cell till A30, ie. cell A11 till cell A30, which are the last 12 non-blank cells with numbers (excluding blanks and text values) in the range A2:A30. This returns the array – {11;16; 20; 0;6;8; “25B”;4;12; 14; 18;0; 7;0; 16; “abc”;0; 0; 0;0}. Using the SUM function returns the total of these last 12 non-blank cells with numbers ie. 132. 

   

 

Cell F5 formula: Return SUM of Largest 5 out of Last 12 values in column A (A2:A30), ignore blanks and text values.

 

The calculation is done for column A (A2:A30) which may contain numbers, blank cells or text values.

 

Cell F5 formula: 

  

=SUM(LARGE( INDEX( A2:A30, LARGE( INDEX( ISNUMBER( A2:A30)* (ROW( A2:A30)- ROW(A2)+1),), 12)):A30, {1,2,3,4,5}))  

  

Explanation – Cell F5 formula (Normal, Non-Array formula): 

 

1) INDEX(A2:A30, LARGE(INDEX( ISNUMBER( A2:A30)* (ROW(A2:A30)- ROW(A2)+1),), 12)):A30: this has already been explained in point 3) of explanation of cell F3 formula. It returns an array of  values of last 12 non-blank cells with numbers (excluding blanks and text values) in the range A2:A30.

 

2) LARGE(INDEX( A2:A30, LARGE(INDEX( ISNUMBER( A2:A30)* (ROW( A2:A30)- ROW(A2)+1),), 12)):A30, {1,2,3,4,5}): using the LARGE function with k argument of “{1,2,3, 4,5}” returns the largest 5 values – {20, 18,16, 16,14}. Using the SUM function returns the total of these largest 5 values in the last 12 non-blank cells with numbers.

 

 

—————————————————————————–

 

Related Formulas – illustrated in the Ebook of Excel Formulas.

  

Other formula(s):

  

Cell F7 formula: Return SUM of last 12 values in column A (A2:A30), excluding the Largest & Lowest value in these – ignore blanks and text values. Normal, Non-Array formula.