User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

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

 

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

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.   

 

-----------------------------------------------------------------------------------------------

 

 

Sum Last 12 Values; Largest 5 of Last 12 Values

             

"Open Image in New Tab" for a full and clear view. 

 

 

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.

 

 

 

 

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.  

 

 

    

Only Two of the Multiple formulas are illustrated above. For full details and explanation refer to Ebook of Excel Formulas.