Summing a range containing errors and values in Microsoft Excel

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.

image 1

 

SUM function is used to add the numbers in a range of cells.

image 2

 

IF: - Check if a condition is met and returns one value if True and another value if False.

image 3

 

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.

image 4

 

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.

image 5

 

Follow below given steps:-

  • Select the cell C2.
  • Write the formula =SUMIF(B2:B11,”<>#VALUE!”,B2:B11)
  • Press enter on your keyboard.
  • The function will return the total value; thereby, ignoring the errors.

image 6

 

There is another formula to return the same result as shown in above image.

Follow below given steps:-

  • Select the cell C2.
  • Write the formula =IF(ISERROR(B2),0,B2)
  • Press Enter on your keyboard.
  • The function will return the value; thereby, ignoring the errors.

image 7

 

  • Write the formula in cell D2.
  • =SUM(C2:C11), press enter on your keyboard.
  • The function will return the total value.

image 8

 

Take away:-

  • To return the sum by ignoring the error.
  • How we can use Excel functions and formulas to solve this problem.

image 48

 

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

 

Users are saying about us...

  1. 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))}

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube