Summing The Diagonal Elements Of A Range
This page describes how to sum the diagonal elements of a range.
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.
 |
This page last updated: 21-Nov-2010. |