Summing a range containing errors and values in Microsoft Excel

» Summing a range containing errors and values in Microsoft Excel

CATEGORY: Summing

VERSIONS: All Microsoft Excel Versions

The solution to summing a range that contains errors covers errors of type #VALUE!

Solution 1

In cells A1:A3, the following values appear

A1	#VALUE!
A2	10
A3	20

Enter the following formula in cell A5: =SUMIF(A1:A3,"<>#VALUE!",A1:A3)

The Result: 30

Solution 2

Enter the following formula in cell B1 and copy formula to cells B2:B3 =IF(ISERROR(A1),0,A1)

Enter the following formula in cell B5: =SUM(B1:B3)

The Result: 30

Rate this tip



1	2	3	4	5

RATING: 2.74
VIEWS: 12109

Email Tip

Print Tip

Add to My Tips

No comments have been submitted.

Sponsors:

Book Store:

VIEW ALL BOOKS

Recommended Books:

Related MS EXCEL TIPS:

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.