Look Who it Was Random video

Excel - Master Diagonal Totaling in Excel with This Easy Formula - Episode 668

2,478 views · Published 20 February 2009 · 2:50 · Indexed 28 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master Diagonal Totaling in Excel with This Easy Formula.

Welcome back to the MrExcel netcast where we tackle all your Excel questions and challenges. I'm Bill Jelen and today we're going to dive into a tricky request that many managers have made - to total a range of data diagonally. It may seem like an impossible task, but thanks to a formula from fellow MVP Chip Pearson, we can make it happen. 

But before we get into the formula, let's take a moment to appreciate the humor in this request. As my former colleague Eric used to say, "Can you take all those worksheets and total them diagonally for me?" It's a request that may seem absurd, but it's one that many of us have encountered in our Excel journeys. And now, thanks to Chip's formula, we can finally make it happen. 

So let's take a closer look at this formula. It's an array formula, which means we'll need to use "Control+Shift+Enter" to make it work. Essentially, the formula checks if the row number of a cell is equal to the column number of that same cell, and then multiplies it by the range of data. This may sound complicated, but trust me, it's a lot simpler than manually totaling data diagonally. 

To better understand how this formula works, let's break it down. First, we determine the row number of the range, which gives us the numbers 1, 2, 3, 4, 5. Then, we determine the column number of the range, which gives us the numbers 1, 2, 3, 4, 5 going in the opposite direction. Next, we use an IF statement to check if the row number is equal to the column number, and if it is, we get a 1, otherwise we get a 0. This creates a matrix of 1s and 0s, with 1s only along the diagonal. Finally, we multiply this matrix by the original numbers and use a SUM formula to get the total. 

But wait, there's more! Tomorrow, we'll be tackling a more challenging request from Kambiz, but don't worry, we'll be using the same trick to solve it. So make sure to tune in and learn how to create a more robust diagonal formula. Thanks for watching and I'll see you in the next video!

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/


Today, we look at how to total a data set diagonally. Episode 668 shows you how.

Table of Contents:
(00:00) Joke of Total Diagonally
(00:38) Formula for Diagonal Totaling
(00:51) Chip Pearson's Contribution
(01:01) Array Formula
(01:11) Formula Breakdown
(01:44) Final Result
(02:02) Summing Up
(02:12) Formula Accuracy
(02:27) 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

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

More from this channel