Ora

How do you sum diagonal cells in Excel?

Published in Excel Diagonal Sum 7 mins read

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 the range.
  • 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 is C3:F6, ROW(C3)-COLUMN(C3) would be 3-3=0.
  • --(...): The double unary operator converts TRUE/FALSE values (resulting from the comparison) into 1/0.
  • SUMPRODUCT: This function multiplies the 1s (where the condition is met) by the corresponding values in the range 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 the range.
  • 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), and last_cell_in_range refers to the bottom-right cell (e.g., E5). For the range B2:E5, this calculates ROW(B2)+COLUMN(E5) which is 2+5=7. This identifies the diagonal E2, 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 your Cell_Reference range.
  • ROW(Cell_Reference): When used in an array context, this returns an array of absolute row numbers for each cell in Cell_Reference.
  • COLUMN(Cell_Reference): When used in an array context, this returns an array of absolute column numbers for each cell in Cell_Reference.
  • Ri: Represents the number of rows in front of the data range's initial cell. If your range starts at A1, Ri would be 0. If it starts at C5, Ri would be ROW(C5)-1 = 4.
  • Ci: Represents the number of columns in front of the data range's initial cell. If your range starts at A1, Ci would be 0. If it starts at C5, Ci would be COLUMN(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 using SUM 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() and COLUMN() differences or sums can make the problem easier to visualize and formulate.
  • Volatile Functions: ROW() and COLUMN() 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.