For summing a range containing errors and values, we can use the SUMIF function or we can use IF function along with ISERROR function in Microsoft Excel.
SUMIF:This function is used to add the numbers in a range of cells on the basis of specific criteria.
SUM function is used to add the numbers in a range of cells.
IF: - Check if a condition is met and returns one value if True and another value if False.
ISERROR- This function is used for checking if a value is an error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, OR #NULL!) and returns True or False.
Let’s take an example to understand how we can perform summing on a range containing errors and values in Microsoft Excel.
We have data in range A2:B11. Column A contains Agent list and column B contains sales amount; in column B, few cells contain #VALUE! Error. We want to return the total value to ignore the error.
Follow below given steps:-
There is another formula to return the same result as shown in above image.
Follow below given steps:-
Take away:-
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Using an array formula, remeber youll have to use the Ctrl+Shift+Enter to get thoes funky brackets, and then use this formula: {=SUM(IF(ISERROR(A1:A3),0,A1:A3))}