Tom’s Tutorials For Excel: Summing a Range Diagonally
Every now and then you come across an unusual request to do this or that in Excel. Such an example is summing a range of numbers diagonally, as shown in the picture for B11:F15.
The array formula that accomplishes this in cell B17 is
=SUM(B11:F15*(ROW(B11:F15)=COLUMN(B11:F15)+9))
Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.
If you are unfamiliar with array formulas, see my video and explanation of arrays here.
One note about this particular formula, in case you are wondering about the +9 portion. Because this is an array formula, the numerical range of rows and columns must equal each other in the summing process, as evidenced by the = operator in the formula. The range of interest is B11:F15, or to express it another way, from row 11 to row 15, and from column 2 to column 6. To satisfy the array’s requirement that elements be evaluated in equal numerical index terms, 9 is added to column 2 to equal 11 as a starting point, which is the same starting row number of the range’s upper left cell B11, that is, row 11. The array sums the elements in the range whose row and column indices equal each other. The +9 notation simply aligns the row and column elements to begin at the same number.
XLellent!