Excel - Sum a Column, Ignoring Error Cells - Episode 1791
Microsoft Excel Tutorial: Summing a Column in Excel: Ignoring Error Cells (AGGREGATE Function & Array Formula).
Welcome to another episode of the MrExcel podcast. In today's episode, we will be discussing how to sum a column in Excel while ignoring error cells. This question was sent in by Mohamed D. through YouTube, and it's a common issue that many Excel users face.
So, what exactly is the problem? Mohamed has a column of numbers, but some of them are formulas that reference cells that have been deleted. This results in reference errors, and when he tries to sum the column, the error cells cause the entire sum to become a reference error as well. How can he add up just the numeric cells and ignore the error cells?
If you have Excel 2010 or newer, the solution is simple - use the AGGREGATE function. This function allows you to specify which function you want to use (in this case, SUM), and also has an option to ignore error values. Simply enter =AGGREGATE(9,6,D9:D17) and you will get the desired sum. However, if you have an earlier version of Excel, you will need to use a different approach.
In that case, we can use an array formula to achieve the same result. This formula, taken from Mike Girvin's CTRL+SHIFT+ENTER book, uses the IF and ISNUMBER functions to check if each cell is a number. If it is, then it will include that cell in the sum, otherwise it will include a 0. Remember to hold down CONTROL+SHIFT+ENTER to create an array formula. Alternatively, you can use the SUMIF function to achieve the same result.
I hope this helps Mohamed and anyone else facing a similar issue. Remember, if you have Excel 2010 or newer, the AGGREGATE function is the way to go. But if you have an earlier version, the array formula or SUMIF function will do the trick. Thank you for tuning in to this episode of the MrExcel podcast. Don't forget to subscribe and hit the notification bell to stay updated on our latest episodes. See you next time!
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-...
Table of Contents:
(00:00) Introduction and Sponsorship
(00:10) Episode Topic: Summing a Column with Error Cells
(00:20) Explanation of Issue
(00:33) Solution using AGGREGATE function
(00:43) Alternative Solution for Older Versions of Excel
(01:02) Example of AGGREGATE function
(01:12) Limitations of AGGREGATE function
(01:22) Alternative Solution using IF and ISNUMBER functions
(01:39) Wrapping the formula with SUM function
(01:52) Example of Array Formula
(02:05) 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:
Alternative method for summing numeric cells in older versions of Excel
Benefits of using AGGREGATE function in Excel 2010 or newer
Creating an array formula to sum numeric cells in Excel
Excel 2010 AGGREGATE function for summing numeric cells
How to sum a column, ignoring error cells in Excel
Ignoring hidden rows and subtotal functions in Excel sum
Mike Girvin’s CTRL+SHIFT+ENTER book for array formulas in Excel
Sum A Column, Ignoring Error Cells
Thanking Mohamed for the question and concluding the netcast
Using AGGREGATE function to ignore error values in Excel
Using ISNUMBER and IF functions to sum numeric cells in Excel
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Today, you have a column of numbers with a few #REF! errors scattered throughout. You want to sum the non-error cells. This is easy using Excel 2010 and AGGREGATE or more difficult using an array formula in earlier versions of Excel.
Watch video Excel - Sum a Column, Ignoring Error Cells - Episode 1791 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 24 September 2013, don't forget to share it with your friends and acquaintances, it has been viewed on our site 13,701 once and liked it 82 people.