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.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1791: Sum A Column, Ignoring Error Cells.
Hey. Welcome back to the MrExcel netcast. I’m Bil Jelen.
Today's question is sent in YouTube from Mohamed D. In a column, he has some numbers, and these numbers are all formulas, but some of the items have been deleted so he has reference errors, and when he comes down here and does a SUM at the bottom, of course, any error in that SUM range is going to cause the SUM to become a reference error itself.
How can he add up just the numeric cells?
Alright.
Well, if you have Excel 2010 or newer, the way to do this is AGGREGATE.
We want the sum function, so 9, TAB , and we want to ignore error values.
Now, he actually used the word subtotal in his question, and if you wanted to ignore hidden rows and other subtotal functions, then you would want to use 3 here – HIDDREN ROWS, ERROR VALUES, NESTED SUBTOTAL AND AGGREGATE FUNTIONS.
I'm just going to use 6 right now, and then the range is this range here, and we will get an answer.
[ =AGGREGATE(9,6,D9:D17) ] Alright.
Aggregate is a great function but you have to have Excel 2010 in order for it to work.
What if you don't have Excel 2010, if you have something earlier, then we're going to do =IF and we're going to do ISNUMBER, =IF ISNUMBER, of all of these cells.
This is a formula I'm taking from Mike Girvin’s CTRL+SHIFT+ENTER book.
If it is a number, then we want that corresponding cell, otherwise we want a 0.
Let's see.
That completes the IF.
That's going to return an entire array of values, so we have to put it inside of a wrapper function, such as SUM, and hold down CONTROL and SHIFT and press ENTER in order to get the answer.
That creates an array formula.
Hold down CONTROL+SHIFT+ENTER.
[ =SUM(IF(ISNUMBER(D9:D17),D9:D17,0)) ] So, 2 different ways to go.
Hopefully, Mohamed has Excel 2010 or newer, and he could just use aggregate.
You get all the goodness of subtotal with the advantage of being able to ignore error cells.
Otherwise, back to this array formula with CONTROL+SHIFT+ENTER.
Well, hey.
I want to thank Mohamed for sending that question in.
I want to thank you for stopping by.
We’ll see you for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1791: Sum A Column, Ignoring Error Cells.
Hey. Welcome back to the MrExcel netcast. I’m Bil Jelen.
Today's question is sent in YouTube from Mohamed D. In a column, he has some numbers, and these numbers are all formulas, but some of the items have been deleted so he has reference errors, and when he comes down here and does a SUM at the bottom, of course, any error in that SUM range is going to cause the SUM to become a reference error itself.
How can he add up just the numeric cells?
Alright.
Well, if you have Excel 2010 or newer, the way to do this is AGGREGATE.
We want the sum function, so 9, TAB , and we want to ignore error values.
Now, he actually used the word subtotal in his question, and if you wanted to ignore hidden rows and other subtotal functions, then you would want to use 3 here – HIDDREN ROWS, ERROR VALUES, NESTED SUBTOTAL AND AGGREGATE FUNTIONS.
I'm just going to use 6 right now, and then the range is this range here, and we will get an answer.
[ =AGGREGATE(9,6,D9:D17) ] Alright.
Aggregate is a great function but you have to have Excel 2010 in order for it to work.
What if you don't have Excel 2010, if you have something earlier, then we're going to do =IF and we're going to do ISNUMBER, =IF ISNUMBER, of all of these cells.
This is a formula I'm taking from Mike Girvin’s CTRL+SHIFT+ENTER book.
If it is a number, then we want that corresponding cell, otherwise we want a 0.
Let's see.
That completes the IF.
That's going to return an entire array of values, so we have to put it inside of a wrapper function, such as SUM, and hold down CONTROL and SHIFT and press ENTER in order to get the answer.
That creates an array formula.
Hold down CONTROL+SHIFT+ENTER.
[ =SUM(IF(ISNUMBER(D9:D17),D9:D17,0)) ] So, 2 different ways to go.
Hopefully, Mohamed has Excel 2010 or newer, and he could just use aggregate.
You get all the goodness of subtotal with the advantage of being able to ignore error cells.
Otherwise, back to this array formula with CONTROL+SHIFT+ENTER.
Well, hey.
I want to thank Mohamed for sending that question in.
I want to thank you for stopping by.
We’ll see you for another netcast from MrExcel.
