SUM ignoring errors in the range - EBOOK VBA EXCEL

EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Search This Blog

Wednesday, January 15, 2020

SUM ignoring errors in the range

SUM ignoring errors in the range

Different ways in getting sum of a range with “Error”

Thanks to the comments made by a friend, I would like to share a few workarounds to ignore errors with SUM.
1) Use a helper column
Image
This would be an easy way if you have flexibility in adding helper column.
The formula in the helper column =IF(ISNUMER(B2),B2,0) is used  to convert “error” into 0, and keep the original value if it is a number.  Then SUM gets the total as usual.

2) SUMIF 

Image
The syntax
SUMIF(range,criteria,sum_range‘when sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.  
The criteria set for SUMIF “<=9.99E+307” is an extremely large number that we could input in Excel.  In this sense, only numbers in the range will be added.
3) SUM(IF) – using array formula
Image
You have to input this formula by CTRL SHIFT ENTER, not just ENTER.  You will see the {} in the formula bar if you have entered the formula successfully.
Basically, IFERROR(B2:B6,0) means “If the cell in the range is an error, then convert it to 0, otherwise keep the original value.”
It will give you the following array:
{1;0;3;4;5}

By putting SUM before that:
=sum({1;0;3;4;5})
As it is an array formula, you have to input the formula by CTRL SHIFT ENTER to tell Excel that you are going to input it as array formula in order to get the correct result, which is 13.
For Excel 2003 or earlier, use:
  • =SUM(IF(ISNUMBER(B2:B6),B2:B6,0)) ‘CTRL SHIFT ENTER
  • =SUM(IF(ISERROR(B2:B6),0,B2:B6),0) ‘CTRL SHIFT ENTER
4) AGGREGATE (Excel 2010 or later)
Image
AGGREGATE is one of my favorite formulas in Excel 2010.  It is an enhanced version of SUBTOTAL that give you more options.
The syntax
AGGREGATE(function_num, options, array,k)
  • Function_num 9 –> SUM
  • Options 6 –> Ignore errors
  • Range B2:B6 –> the range of data to be added
It simply means “Pls give me the SUM of the data in the range B2:B6.  Ignore errors pls!”
This function alone worths a separate post.  For the moment if you want to know more about Aggregate (in Excel 2010), you may check it out from Excel help.
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment