User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

SUM Diagonal Cells in a Range

 

 

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 / Count Diagonal Cells in a Range

Diagonal cells are considered starting from the top-left cell of the range 

 

 

Cell G4 formula: SUM diagonal cells in range "B4:E9" starting from cell "B4" ie. top-left cell of the range - cells B4, C5, D6, E7.

   

 

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

 

 

Sum Diagonal Cells

         

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

 

 

Cell G4 formula: SUM diagonal cells in range "B4:E9" starting from cell "B4" ie. top-left cell of the range - cells B4, C5, D6, E7.  

 

 

Cell G4 formula: 

  

=SUMPRODUCT(( COLUMN( B4:E9)- COLUMN(B4)+1= ROW(B4:E9)- ROW(B4)+ 1)*(B4:E9))  

 

  

Explanation - Cell G4 formula (Non-Array Formula):

 

1) COLUMN(B4:E9)- COLUMN(B4)+1: returns an array of numbers for the number of columns in the range ie. from 1 to 4 - {1,2, 3,4}. ROW(B4:E9)- ROW(B4)+1: returns an array of numbers for the number of rows in the range ie. from 1 to 6 - {1;2;3; 4;5;6}.

 

2) (COLUMN(B4:E9)- COLUMN(B4)+1= ROW(B4:E9)- ROW(B4)+1): returns an array of TRUE and FALSE values, for the range comprising of 4 columns and 6 rows - {TRUE, FALSE, FALSE, FALSE; FALSE, TRUE, FALSE, FALSE; FALSE, FALSE, TRUE, FALSE; FALSE, FALSE, FALSE, TRUE; FALSE,FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE}. The TRUE values are where row & column number are same which indicate the diagonal cells. For example, the 1st row values "TRUE,FALSE, FALSE,FALSE" indicate that the 1st cell of B4 in the 1st row (B4:E4) is the 1st diagonal cell. The 2nd row values of "FALSE, TRUE, FALSE, FALSE" indicate that the 2nd cell of C5 in the 2nd row (B5:E5) is the 2nd diagonal cell. And so on, so that the cells B4, C5, D6 and E7 return TRUE.

 

3) (COLUMN(B4:E9)- COLUMN(B4)+1= ROW(B4:E9)- ROW(B4)+1)*( B4:E9): Multiplying the array in 2) above with the range B4:E9 returns the values which are in the cells returning TRUE in the above array where TRUE converts to 1 on multiplication - {6,0, 0,0; 0,12, 0,0; 0,0, 17,0; 0,0, 0,3; 0,0, 0,0; 0,0, 0,0}. For example, the 1st value of 6 is in cell B4, the 2nd number 12 is from cell C5, and the next two values of 17 and 3 are from cells D6 & E7 respectively, which are the 4 diagonal cells. Using the SUMPRODUCT function returns their SUM ie. 38 in cell G4.

 

  

 

Other formulas:

 

Cell G6 formula: SUM diagonal cells in range "B5:E9" starting from cell "B5" ie. top-left cell of the range - cells B5, C6, D7, E8. Normal, Non-Array formula.

 

Cell G8 formula: COUNT diagonal non-blank cells in range "B6:E9" starting from cell "B6" ie. top-left cell of the range - cells B6, C7, D8, E9. Normal, Non-Array formula. 

 

 

    

Only one of the multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.