Using Arrays to Add Columns Containing Error Messages

I wanted to finish up my discussion of the other day - how to calculate columns containing error messages and also introduce you to Arrays. Arrays are very interesting and I think I will spend some time next week talking about them. They are extremely powerful.


This is a totally different approach from my last post which showed you how to use SUMIF if you wanted to add up a column of numbers with errors.


This approach uses an array to replace all the error messages with zeroes. Here we telling Excel to test for any errors and replace them with zeroes and to then sum the column.
In cell B8, I typed =SUM(ISERROR(B2:B7),0,B2:B7)) and then pressed Control+Shift +Enter at the same time.

The key thing to remember about arrays is that after you create the formula, you must press Control+Shift+Enter instead of just pressing enter. The 3 keystrokes tell Excel that it is an array and that all the cells are treated as a single block of cells and is depicted by the { } marks you see around the formula.
Obviously you would not use a 0 if you were trying to average the column. To get around that you could use " " to put blanks in the cells with errors.

If you are interested in arrays, you may want to check out my Arrays- A Powerful Tool course (If you are not a CPA and/or don't care about the CPE, just the Ebook is available for purchase on this site- in the far right column).
It walks you through how to put arrays together step-by-step.
Have fun and have a great weekend because... hey- it's Friday.