 Summing The Diagonal Elements Of A Range

This page describes how to sum the diagonal elements of a range. Introduction

You can use a formula to sum the diagonal elements of a range. I'm not sure what practical use this has, but I have receieved at least two email over the years on the topic, so here's how to do it. It is assumed that the range in question has an equal number of rows and columns and that all of its elements are numeric. You can enhance the formulas to include such tests if you need them. All of the formulas on this page are array formulas, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. Click here for an in depth discussion of array formulas.

The simplest case is when the upper left corner is in cell A1. This can be generalized to the case in which the row number of the upper left corner of the range is equal to the column number of the upper left corner of the range. Assuming that the range has the name Matrix, the following array formula will sum the diagonal elements.

=SUM(IF(ROW(Matrix)=COLUMN(Matrix),Matrix,0))

Here each row number of the range Matrix is compared to each column number of Matrix and if they are equal, the element's value is passed to the SUM function. If they are not equal, 0 is passed to SUM.

The formula becomes a bit more complicated when the row number of the top left cell is not the same as the column number of the top left cell. Assuming our range is named Matrix2, the following formula will sum the diagonal elements:

=SUM(IF(ROW(Matrix2)-MAX(ROW(Matrix2))=COLUMN(Matrix2)-MAX(COLUMN(Matrix2)),Matrix2,FALSE))

As before, this is a array formula and assumes that the number of rows in the range is equal to the number of columns in the range.

It is possible, too, to write a formula that allows you to sum the diagonal elements of a subset of the orginal range. This subset begins in the upper left corner of the range and extends for some number of rows (and columns) into the range. This resizing value is assumed to be in a cell with the name Size. If Size is equal to 1, the only element summed is the upper left corner cell. If Size is equal to the number of rows in the range, the formula's result is the sum of the diagonal elements of the entire range. If Size is greater than the number of rows in the range, the results are unpredicable, based on what values might lie outside the range. For example, if your range is in A1:D4 and Size is equal to 3, the range whose diagonal elements are summed is A1:C3. In the following array formula, the range is assumed to have the name Matrix3.

=SUM(IF(ROW(OFFSET(Matrix3,0,0,Size,Size))-MAX(ROW(OFFSET(Matrix3,0,0,Size,Size)))=
COLUMN(OFFSET(Matrix3,0,0,Size,Size))-MAX(COLUMN(OFFSET(Matrix3,0,0,Size,Size))),
OFFSET(Matrix3,0,0,Size,Size),FALSE))

If you wanted to sum the diagonal elements in a subset of the main range where the subset begins within the range and ends at the lower right corner of the range, just use the formula to calculate the sum of all diagonal elements and then subtract from that the sum of the upper diagonal, shown above. The difference will be the sum of the diagonal elements in the lower subset. You can download the file with all the example formulas on this page.  This page last updated: 21-Nov-2010.

-->