# Sum Diagonal Cells in a Range

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.

Scroll to top