Look Who it Was Random video

Excel - How to Total Numbers Diagonally in Microsoft Excel (Including Backwards Rows!) - Episode 669

2,148 views · Published 20 February 2009 · 3:04 · Indexed 27 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  How to Total Numbers Diagonally in Microsoft Excel (Including Backwards Rows).

Welcome back to the MrExcel netcast! In this video, we're going to tackle a question from one of our viewers, Cannabis. This question instantly reminded me of a conversation I had with Eric about totaling numbers diagonally, and how Chip Pearson taught me how to do it. But this time, Cannabis has a slightly more difficult question. They need to add up numbers diagonally, but not just the adjacent cells. They need to go back an additional row as they move across columns. Let's see how we can solve this problem together.

Yesterday, we used a formula from Chip that involved comparing the row number and column number of each cell in the data set to get the numbers along the diagonal. But this time, we need to go backwards. So, I started by thinking about the five row by five column area and how we could get the column numbers for each cell. Then, I realized we needed a way to get the row numbers as well. After some trial and error, I came up with a formula that takes the active cell and adds two rows to it, giving us the row numbers we need.

To make this formula work, I had to adjust Chip's formula a bit. We compare the column number of the range B12 to F16 to the row number that is two rows below us (in this case, 18 minus the row number of each cell in the five row area). If this is true, we multiply it by the range B12 to F16, giving us the totals of the last five rows in the diagonal. And the best part is, we can easily copy this formula to get the totals for any other diagonal in the data set.

I have to admit, I have no idea why Cannabis needs to do this. But as soon as I saw the question, it reminded me of Chip's website and the formula I had to figure out. It's one of those things that I've only dealt with twice in 10 years, so I always have to piece it together. But thanks to Cannabis for sending in this question and giving me the opportunity to refresh my memory on this topic. And thank you for watching! Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Kambiz sends in a question today that is far more difficult than the example from podcast 668. Kambiz needs running diagonal totals. In Episode 669, we take a look at Chips formula from Episode 668 and figure out how to modify it to solve this tricky problem.

Table of Contents:
(00:00) Introduction to Cannabis question
(00:20) Background on the question
(00:30) Previous solution for totaling numbers diagonally
(00:40) Specifics of the question
(00:50) Use of formula from Chip Pearson
(01:05) Need for a different approach
(01:15) Idea for solving the problem
(01:31) Building the formula
(02:15) Final solution and its application
(02:25) Reason for addressing the question
(02:42) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding up adjacent numbers
Adjusting formula
Array formula
Cannabis question
Chip Pearson teaching
Comparing row number and column number
Copying formula
Formula from Chip
Going backwards in data set
Totaling numbers diagonally
Web page from Chip

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152246/

More from this channel