SUM Diagonal Cells in a Range
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.
———————————————————————————————–
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.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
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.