Excel offers various methods to sum cells along a diagonal, typically categorized into the main diagonal (top-left to bottom-right) and the anti-diagonal (top-right to bottom-left). These methods often involve array formulas that identify cells based on their row and column positions.
Summing Diagonal Cells in Excel
Summing diagonal cells in Excel can be achieved using array formulas with functions like SUMPRODUCT
, ROW
, and COLUMN
. The approach varies slightly depending on whether you want to sum the main diagonal or the anti-diagonal.
1. Summing the Main Diagonal (Top-Left to Bottom-Right)
The main diagonal consists of cells where the difference between their row number and column number is constant. For a square range starting at A1, this difference is 0 (e.g., A1=1-1, B2=2-2, C3=3-3).
Formula:
=SUMPRODUCT(--(ROW(range)-COLUMN(range)=ROW(first_cell_in_range)-COLUMN(first_cell_in_range)), range)
Explanation:
ROW(range)-COLUMN(range)
: This part creates an array of differences between the absolute row and column numbers for each cell in therange
.ROW(first_cell_in_range)-COLUMN(first_cell_in_range)
: This calculates the constant difference for the main diagonal, ensuring the formula works even if your range doesn't start at A1. For example, if your range isC3:F6
,ROW(C3)-COLUMN(C3)
would be3-3=0
.--(...)
: The double unary operator convertsTRUE
/FALSE
values (resulting from the comparison) into1
/0
.SUMPRODUCT
: This function multiplies the1
s (where the condition is met) by the corresponding values in therange
and then sums them up.
Example:
Let's say you have data in the range B2:E5
:
B | C | D | E | |
---|---|---|---|---|
2 | 10 | 12 | 14 | 16 |
3 | 18 | 20 | 22 | 24 |
4 | 26 | 28 | 30 | 32 |
5 | 34 | 36 | 38 | 40 |
To sum the main diagonal (B2, C3, D4, E5):
=SUMPRODUCT(--(ROW(B2:E5)-COLUMN(B2:E5)=ROW(B2)-COLUMN(B2)), B2:E5)
In this case, ROW(B2)-COLUMN(B2)
is 2-2=0
. So the formula identifies cells where ROW()-COLUMN()=0
.
Result: 10 + 20 + 30 + 40 = 100
2. Summing the Anti-Diagonal (Top-Right to Bottom-Left)
The anti-diagonal consists of cells where the sum of their row number and column number is constant. For a square range starting at A1 (e.g., A1:D4), the anti-diagonal cells are A4, B3, C2, D1. The sum of their row and column numbers is ROWS(range) + 1
(e.g., A4=4+1=5, B3=3+2=5, C2=2+3=5, D1=1+4=5).
Formula:
=SUMPRODUCT(--(ROW(range)+COLUMN(range)=ROWS(range)+COLUMNS(range)+ROW(first_cell_in_range)+COLUMN(first_cell_in_range)-1-COLUMNS(range)*2), range)
This can be simplified for a square range:
=SUMPRODUCT(--(ROW(range)+COLUMN(range)=ROWS(range)+COLUMN(first_cell_in_range)+ROW(first_cell_in_range)), range)
Even more simply, for a square range, the constant for the anti-diagonal is ROWS(range) + COLUMN_OFFSET + ROW_OFFSET + 1
.
A more universally applicable approach for a range R
starting at (R_start, C_start)
:
The condition ROW(cell) + COLUMN(cell) = ROW(first_cell_of_anti_diagonal) + COLUMN(first_cell_of_anti_diagonal)
.
For a range A1:D4
, the first cell of the anti-diagonal is D1
. Its row+column is 1+4=5
.
For a range B2:E5
, the first cell of the anti-diagonal is E2
. Its row+column is 2+5=7
.
The general constant for the anti-diagonal, if the range is square, can be calculated as ROW(first_cell_of_range) + COLUMN(last_cell_of_range)
.
Simplified Anti-Diagonal Formula (Square Range):
=SUMPRODUCT(--(ROW(range)+COLUMN(range)=ROW(first_cell_in_range)+COLUMN(last_cell_in_range)), range)
Explanation:
ROW(range)+COLUMN(range)
: This creates an array of sums of row and column numbers for each cell in therange
.ROW(first_cell_in_range)+COLUMN(last_cell_in_range)
: This calculates the constant sum for the anti-diagonal.first_cell_in_range
refers to the top-left cell (e.g.,B2
), andlast_cell_in_range
refers to the bottom-right cell (e.g.,E5
). For the rangeB2:E5
, this calculatesROW(B2)+COLUMN(E5)
which is2+5=7
. This identifies the diagonalE2, D3, C4, B5
.
Example:
Using the same data in B2:E5
:
B | C | D | E | |
---|---|---|---|---|
2 | 10 | 12 | 14 | 16 |
3 | 18 | 20 | 22 | 24 |
4 | 26 | 28 | 30 | 32 |
5 | 34 | 36 | 38 | 40 |
To sum the anti-diagonal (E2, D3, C4, B5):
=SUMPRODUCT(--(ROW(B2:E5)+COLUMN(B2:E5)=ROW(B2)+COLUMN(E5)), B2:E5)
In this case, ROW(B2)+COLUMN(E5)
is 2+5=7
. So the formula identifies cells where ROW()+COLUMN()=7
.
Result: 16 + 22 + 28 + 34 = 100
3. Using Advanced Array Logic for Custom Diagonals
For more complex or specific diagonal patterns, you can utilize an array formula structure that evaluates a condition for each cell within a range. This method involves dynamically calculating offsets based on the range's position and size.
The following formula structure allows for summing a diagonal based on an adjusted row/column index:
=SUM(Cell_Reference*((ROWS(Cell_Reference) + Ri)-ROW(Cell_Reference)=COLUMN(Cell_Reference)-Ci))
This is an array formula and must be entered by pressing Ctrl + Shift + Enter
in older Excel versions, or simply Enter
in modern Excel versions (Microsoft 365).
Explanation of Components:
Cell_Reference
: This is the entire data range you are working with (e.g.,A1:D4
).ROWS(Cell_Reference)
: Returns the total number of rows in yourCell_Reference
range.ROW(Cell_Reference)
: When used in an array context, this returns an array of absolute row numbers for each cell inCell_Reference
.COLUMN(Cell_Reference)
: When used in an array context, this returns an array of absolute column numbers for each cell inCell_Reference
.Ri
: Represents the number of rows in front of the data range's initial cell. If your range starts atA1
,Ri
would be 0. If it starts atC5
,Ri
would beROW(C5)-1 = 4
.Ci
: Represents the number of columns in front of the data range's initial cell. If your range starts atA1
,Ci
would be 0. If it starts atC5
,Ci
would beCOLUMN(C5)-1 = 2
.
How it Works:
The core of this formula is the condition: (ROWS(Cell_Reference) + Ri)-ROW(Cell_Reference)=COLUMN(Cell_Reference)-Ci
.
This condition can be rearranged as:
ROWS(Cell_Reference) + Ri + Ci = ROW(Cell_Reference) + COLUMN(Cell_Reference)
This means the formula identifies and sums cells where the sum of their absolute row and column numbers (ROW(cell) + COLUMN(cell)
) equals a specific constant. This constant is derived from the total rows of the range, plus the Ri
and Ci
offsets.
Example:
Consider a data range A1:D4
with values:
A | B | C | D | |
---|---|---|---|---|
1 | 1 | 2 | 3 | 4 |
2 | 5 | 6 | 7 | 8 |
3 | 9 | 10 | 11 | 12 |
4 | 13 | 14 | 15 | 16 |
If Cell_Reference
is A1:D4
, and assuming Ri=0
and Ci=0
(because A1 is the first cell of the sheet), the formula becomes:
=SUM(A1:D4*((ROWS(A1:D4) + 0)-ROW(A1:D4)=COLUMN(A1:D4)-0))
=SUM(A1:D4*(4-ROW(A1:D4)=COLUMN(A1:D4)))
This identifies cells where 4 - ROW(cell) = COLUMN(cell)
, or equivalently, ROW(cell) + COLUMN(cell) = 4
.
The cells meeting this condition are:
A3
:ROW(A3)=3
,COLUMN(A3)=1
->3+1=4
(TRUE)B2
:ROW(B2)=2
,COLUMN(B2)=2
->2+2=4
(TRUE)C1
:ROW(C1)=1
,COLUMN(C1)=3
->1+3=4
(TRUE)
The sum would be 9 + 6 + 3 = 18
. This formula, with Ri=0
and Ci=0
, effectively sums a specific anti-diagonal within the matrix where the sum of row and column numbers equals the total number of rows in the range.
Tips for Using Diagonal Sum Formulas
- Square Ranges: Diagonal formulas are most straightforward and typically apply to square ranges (same number of rows and columns). For non-square ranges, "diagonal" can become ambiguous, and you might need more complex logic.
- Array Entry: Remember to use
Ctrl + Shift + Enter
for older Excel versions if usingSUM
with a conditional array.SUMPRODUCT
doesn't require this. - Helper Columns: For very complex or custom diagonal patterns, sometimes adding helper columns/rows that calculate
ROW()
andCOLUMN()
differences or sums can make the problem easier to visualize and formulate. - Volatile Functions:
ROW()
andCOLUMN()
are not volatile, but their use in large array formulas can sometimes impact performance. For most practical scenarios, this is not an issue.
By understanding these methods, you can efficiently sum diagonal cells in your Excel spreadsheets.