Excel tutorials

Diagonal Sum Cells -
Sum of Values Diagonally in Excel

How the SUM of Values Diagonally Down.

If you've ever needed to SUM values or numbers diagonally in some cell range and you did not know, then this Excel tutorial with formulas examples is right for you. When creating a formula to keep in mind when you use the absolute address when the relative address range of cells.

Excel Example 1

In a picture below you can see a certain range of cells that contain values or numbers. For this first example, see which cell range the values are. So the first left upper cell is located at 'A1'. This is important because this cell 'A1' is the starting cell for the diagonal sum, which you can notice in the formulas found in the first Excel example. In this first example, we SUM the diagonal values down, from the first cell 'A1' diagonally right (red numbers in the figure below)

How the sum of values is diagonally down in Excel

The formulas shown below are related to the cell range situation in the image above. If you set one of the formula and after adding a row or column in front of the current cell range position some of the formula shown below will change the result. So you need to consider possible changes in the position range of cells as well as what values you want to sum diagonally. With respect to the picture from this first example the basic ARRAY formula of the diagonal sum is the following.

=SUM(IF(ROW(1:5)=COLUMN(A1:E5)-Cx;A1:E5))

In the formula above, notice important arguments of formula a that is 'ROW (1:5)' and 'Cx'.

'ROW(1:5)' => the number of rows containing values

'Cx' => the number of columns containing values that we want to diagonal sum.

I will show here more ARRAY formulas that return the same result

  • =SUM(IF(ROW(1:5)=COLUMN(A1:E5)-0;A1:E5))
  • =SUM(IF(ROW(A1:E5)=COLUMN(A1:E5)-0;A1:E5;0))
  • =SUM(IF(ROW(INDIRECT(1&CHAR(58)&5))=COLUMN(A1:E5)-0;A1:E5))
  • =SUM(IF(ROW(A1:E5)=COLUMN(A1:E5)-(COLUMN($A$1)-1);A1:E5;0))
  • =SUM(IF(ROW(A1:E5)-MAX(ROW(A1:E5))=COLUMN(A1:E5)-MAX(COLUMN(A1:E5));A1:E5;FALSE))
  • =SUM(IF(ROW(INDIRECT(1&CHAR(58)&5))=COLUMN(A1:E5)-(COLUMN($A$1)-1);A1:E5))
  • =SUMPRODUCT((ROW($A$1:$E$5)-MAX(ROW(A1:E5))=COLUMN($A$1:$E$5)-MAX(COLUMN(A1:E5)))*($A$1:$E$5))

Place this formula in your Workbook and try later to add several columns or rows before the current situation in the picture from this first example. Note that some formulas change the result in relation to the original situation. The last three formulas above do not change the result, they are fixed.

The last formula in the list above, you can copy to the right or down. Then will the formulas return the results for each following sum.

How to in Excel Table SUM values diagonally up

Excel Example 2

In the picture below you see a specific range of cells that contain values or numbers. For this second Excel example, you can see in which cell range the values are located. So the first left upper cell is located at 'A1'.
Since we want the SUM upwards, then we have a start cell 'A5'. This is important because this cell 'A5' is the starting cell for the diagonal sum, which you can notice in the formulas found in the second example. In this example, the SUM of values is diagonally upward from the last cell in the first column to the first cell of the last column in the range.(blue numbers in the picture below).

sum cells up a diagonal

See the picture above and the basic ARRAY formula for the diagonal sum to the top right is shown below.
You notice two important arguments in formula ('Rx' and 'Cx').

=SUM(A1:E5*((ROWS(A1:E5)+Rx)-ROW(A1:E5)=COLUMN(A1:E5)-Cx))

'Rx' => the number of rows in front of the first cell of the data range

'Cx' => the number of columns in front of the first cell of the data range

Formulas can be created as shown below

  • =SUM(A1:E5*((ROWS(A1:E5)+1)-ROW(A1:E5)=COLUMN(A1:E5)))
  • =SUM(A1:E5*((ROWS(A1:E5)+ROW(A1))-ROW(A1:E5)=COLUMN(A1:E5)))
  • =SUM(A1:E5*((ROWS(A1:E5)+COLUMN(A1))-ROW(A1:E5)=COLUMN(A1:E5)))
  • =SUM(A1:E5*((ROWS(A1:E5)+(ROW($A$1)-2))-ROW(A1:E5)=COLUMN(A1:E5)-COLUMN(A1)-1))
  • =SUMPRODUCT((ROW($A$1:$E$5)-MIN(ROW(A5:E7))=MIN(COLUMN(A5:E7))-COLUMN($A$1:$E$5))*($A$1:$E$5))

If you set 'Rx' to zero (0) then the formula sums up the cells 'A4', 'B3', 'C2' and 'D1' (in the picture above the value marked red). The second formula in the list below has no problem with setting zero (0) for the number of rows in front.

for example:

  • =SUM(A1:E5*((ROWS(A1:E5)+0)-ROW(A1:E5)=COLUMN(A1:E5)))
  • =SUMPRODUCT((ROW($A$1:$E$5)-MIN(ROW(A4:E6))=MIN(COLUMN(A4:E6))-COLUMN($A$1:$E$5))*($A$1:$E$5))

You notice that the second formula in the list above the first start cell is 'A4'.

So in this case, I calculate the SUM of the diagonal values in Excel cells starting with 'A4', 'B3', 'C2' and 'D1' (above the value shown in red) but you can see the picture below.

The Results of the Diagonal SUM of the Value from the image above upwards

Two rows in front and three columns in front of the range of cells refer to the picture above, ie. Data from a table in which we want SUM of diagonal values up.

excel results of sum cells up diagonally

To realize and understand how the above formulas work, do the following.
Create a table of values in your Excel workbook (identical to the first image in Example 2) and enter the respective formula. Then add several rows and several columns in front of the defined cell range. Notice how the results change depending on the number of rows and columns added. The last two formulas of the five listed above will not change the result.

The Diagonal Sum of Values in an Excel table if the number of columns and rows is the same

Excel Example 3

In case you have the same number of columns and rows in the cell range then we have a situation that is described in the first two examples. In this third example, the situation is similar but a different number of columns and rows in front of the cell range.
Look at the picture below!
You notice, one (1) column and two (2) rows in front of the cell range containing the values. So we have added a certain number rows or columns (Header Row and Header Column). The first start cell of our database is cell 'B3' when the SUM is diagonally down. If the SUM diagonal up then the first start cell 'B7'.

summing a range diagonally in excel

SUM Diagonal Cells Down (in the above figure is marked red, brown arrow)

In case we have a certain number of rows and columns in front of the cell range, the formulas for the diagonal SUM to the down may be the following.

ARRAY basic formula for the sum of diagonal down the next.

=SUM(B3:F7*((ROW(B3:F7)-Rx)=(COLUMN(B3:F7)-Cx)))

Other formulas that can be used in this third example for diagonal SUM descending down the following:

  • =SUM(B3:F7*(ROW(B3:F7)=COLUMN(B3:F7)+1))
  • =SUM(B3:F7*((ROW(B3:F7)-1)=(COLUMN(B3:F7)-0)))
  • =SUM(B3:F7*(ROW(B3:F7)=(COLUMN(B3:F7)-(1-2))))
  • =SUM(B3:F7*(ROW(B3:F7)=COLUMN(B3:F7)+(2-(COLUMN($A$1)))))
  • =SUM(B3:F7*(ROW(INDIRECT(3&CHAR(58)&7))=COLUMN(B3:F7)+(2-(COLUMN($A$1)))))

You notice, in the list above all the formulas by which you can diagonally SUM all the cells in the downward direction (values marked with red font). Last ARRAY formula listed above is fixed and it will not change the result if you add several columns or several rows in front of the range.

The SUM of Diagonal Cells Ascending from Left to Right (in the picture above marked in blue, blue arrow)

In case, when we have a certain number of rows and columns in front of the cell range, the formulas for the diagonal SUM by ascending may be the following.

The basic ARRAY formula for the diagonal sum of ascending from left to right is the following:

=SUM(B3:F7*((ROW(B3:F7)+Rx)=(COLUMN(B3:F7)-Cx)))

Other formulas that you can use in this third example for the diagonal SUM by ascending are the following:

  • =SUM(B3:F7*(ROWS(B3:F7)+1-ROW(B3:F7)=COLUMN(B3:F7)-3))
  • =SUM(B3:F7*(ROWS(B3:F7)+2-ROW(B3:F7)=COLUMN(B3:F7)-2))
  • =SUM(B3:F7*((ROWS(B3:F7)+(ROW($A$2)-1))-ROW(B3:F7)=COLUMN(B3:F7)-COLUMN(C3)-0))

You notice, in the list above all the formulas by which you can diagonally SUM up all the cells (values marked with blue font). Last ARRAY formula listed above is fixed and it will not change the result if you add several columns or several rows in front of the range.

The Diagonal Sum of Values in the Excel table if the number of columns and rows is different.

Excel Example 4

In case you have a different number of columns and rows (in this example, 6 rows and 5 columns) In the cell range then we have the situation described below in tutorials. In this fourth example we have a different number of columns and cell ranges as well as in front of the cell range we have 1 column and 2 rows. Look at the picture below!
You notice one (1) column and two (2) queues in front of the cell range containing the values. So we have added a certain number of columns or rows (6 Header Row Data and 5 Header Column Data). The first start cell of our database is cell 'B3' when the sum is diagonally down. If the sum is diagonally down then the first start cell is 'A3'.

sum of diagonal cells down

Sum of Diagonal Values per column

If we want to start from the beginning and the sum of the numbers or values diagonally by the start column of cell 'B3', then the basic formula is next. In the formula below, note the number of row marked 'green' and the number of column marked 'blue/red'.

  • =SUMPRODUCT(((MATCH($A$3:$A$8;$A$3:$A$8;0)-MATCH($B$2:$F$2;$B$2:$F$2;0))=5-1)*($B$3:$F$8))
  • =SUMPRODUCT((((ROW($A$3:$A$8)-2)-(COLUMN($B$2:$F$2)-1))=5-1)*($B$3:$F$8))

The following formula below for the diagonal SUM gives the same result.

  • =SUMPRODUCT(((MATCH($A$3:$A$8;$A$3:$A$8;0)-MATCH($B$2:$F$2;$B$2:$F$2;0))=5-COLUMN(A1))*($B$3:$F$8))
  • =SUMPRODUCT(((MATCH($A$3:$A$8;$A$3:$A$8;0)-MATCH($B$2:$F$2;$B$2:$F$2;0))=COUNTA($B$2:$F$2)-COLUMN(A1))*($B$3:$F$8))
  • =SUMPRODUCT(((MATCH($A$3:$A$8;$A$3:$A$8;0)-MATCH($B$2:$F$2;$B$2:$F$2;0))=COUNTA($B$2:$F$2)-COUNTA($B2:B2))*($B$3:$F$8))
  • =SUMPRODUCT((((ROW($A$3:$A$8)-2)-(COLUMN($B$2:$F$2)-1))=5-COLUMN(A1))*($B$3:$F$8))
  • =SUMPRODUCT((((ROW($A$3:$A$8)-2)-(COLUMN($B$2:$F$2)-1))=COUNTA($B$2:$F$2)-COUNTA($B2:B2))*($B$3:$F$8))
  • =SUMPRODUCT((((ROW($A$3:$A$8)-ROW($B$2))-(COLUMN($B$2:$F$2)-COLUMN($A$1)))=COUNTA($B$2:$F$2)-COUNTA($B2:B2))*($B$3:$F$8))

The formula sums up (red values) in cell 'F11' is the next. (Copy this formula to the left to get the diagonal sum of the next cell).

=SUMPRODUCT(((MATCH($A$3:$A$8;$A$3:$A$8;0)-MATCH($B$2:$F$2;$B$2:$F$2;0))=COUNTA($B$2:$F$2)-COUNTA($B2:F2))*($B$3:$F$8))

The Diagonal Sum Down for Certain a Number of Columns.

If you want the sum of the values diagonally down but for a specific number of columns then use this ARRAY formula below. The formula is located in the cell 'I11' in the picture above and a specific number of columns as the condition 'number 3' is located in cell 'H11' in the image above. This formula below, the diagonal sum of values only for the first three columns in the data range (enter formula in one line).

=SUM(IF(ROW(OFFSET($B$3:$F$8;0;0;$H$11;H11))-MAX(ROW(OFFSET($B$3:$F$8;0;0;$H$11;$H$11)))=COLUMN(OFFSET($B$3:$F$8;0;0;$H$11;$H$11))-MAX(COLUMN(OFFSET($B$3:$F$8;0;0;$H$11;$H$11)));OFFSET($B$3:$F$8;0;0;$H$11;$H$11);FALSE))

Copying formulas for the Diagonal SUM in Excel

If you want the SUM of all diagonal cells in the Excel table then use this formula below.

=SUMPRODUCT((ROW($B$3:$F$8)-MAX(ROW(B2:F7))=COLUMN($B$3:$F$8)-MAX(COLUMN(B2:F7)))*($B$3:$F$8))

This formula returns the diagonal SUM of all values with the start of cell 'B3' in the downward direction (see figure below).
If you copy the formula down then you will get a diagonal sum of values down starting from cells 'B4', 'B5', 'B6' and so on.
If you copy the formula to the right, then you will get a diagonal sum of values down starting from the cells 'C3', 'D3', 'E3', etc.

copy of formula sum diagonal values in excel

And at the end. If you really want to understand all the formulas shown in this Excel tutorial, do not be lazy. Create an Excel table from these examples and copy these formulas to your Workbook. After entering all formulas, try adding several rows and columns in front of the Excel table. Track the results of all formulas and you'll notice which formula will change the result.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.