We help IT Professionals succeed at work.

How to ignore #DIV/0! cells?

ajny
ajny asked
on
3,662 Views
Last Modified: 2012-06-27
Hi,

I'm working with Excel 2007 and I have a spreadsheet with data starting each row in column B and I want to sum it all in column A. In other words A1=Sum(B1:Z1), A2=Sum(B2:Z2) etc.

The problem is that the data is the result of calculations from other sheets and because of lack of info in those other sheets, sometimes the result is #DIV/0! (or other error). That makes A1=Sum(B1:Z1) also #DIV/0!.

My question is how to ignore those error cells? I want A1 to be the sum of B1:Z1 but to exclude the errors cells (or treat them as 0). However, I don't want to change the formulas in B1:Z1 (to use ISERR for example). Can it be done?

Another quick question: how to apply a formula to a range of cells without dragging with the mouse? For example if I want to apply the above A1=Sum(B1:Z1) to rows 2-1000 (with correct indexes of course), how can I do it without dragging the A1 cell down 1000 rows?

Thanks to anyone who may help

 
Comment
Watch Question

Software testing consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
=IF(a7=0,"",sum(b7 .. z7)
=IF(a7=0,"",sum(b7:z7) , sorry should be :.
I am not fully awake yet.
if you only deals with positive numbers and would like to avoid any error (#N/A, #REF, etc...) try this

=SUMIF(B1:Z1,">0")

Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you all for your quick help.

Best regards.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.